PostGIS related database parameters

I. definition of table structure

-- Table: public.tbl_buildings

-- DROP TABLE public.tbl_buildings

CREATE TABLE public.tbl_buildings

(

Id character (7) NOT NULL

Name character (20)

Type character (10)

Address character (30)

The_geom geometry

CONSTRAINT tbl_buildings_pkey PRIMARY KEY (id)

)

Column change

SELECT DropGeometryColumn ('tbl_properties',' the_geom')

SELECT AddGeometryColumn ('tbl_properties',' the_geom', 4326

'POINT', 2)

Add data

INSERT INTO tbl_properties (id, town, postal_code, street

"number", the_geom) VALUES (1, 'London',' N7 6PA, 'Holloway Road'

32, ST_GeomFromEWKT ('SRID=4326;POINT (51.556173-0.116190)')

II. Build gist index by geometry column

CREATE INDEX idx_geo_buildings ON tbl_buildings USING GIST (the_geom)

III. Database parameters

1.checkpoint_segments (9.5 ago)

Default value:3

Suggested value:6

-

Max_wal_size (after 9.5)

Default value:1 GB

2.constraint_exclusion

Default value:Deactivated

Suggested value:Activated, partition

3.maintenance_work_mem

Default value:16

Suggested value:128

4.random_page_cost

Default value 4.0

Suggested value 2.0

5.shared_buffers

Default value: 32 MB

Suggested value: 64 MB to 512 MB unlikely for an allocation of more than 40 percent

Of RAM (windows), 25% physical memory (Linux)

6.wal_buffers

Default value:64kb

Suggested value:1MB

7.work_mem

Default value:1MB

Suggested value:16MB

The above are all recommended values and need to be modified according to the operation of the system.