Create GIS database in PostgreSQL

1. Install PostgreSQL database (for example 9.6)
2. Install postgis module (for example 2.5.1)
3. Start pgAdmin 4
4. Create database
5. Enable GIS features in database with following command:
6. Now shapefile can be loaded with PostGIS 2.0 shapefile tool
7. Set GEO input format (for example Rijksdriehoek)
SELECT UpdateGeometrySRID(‘wegvakken’,’geom’, 28992);
8. Create WGS84 geog column and fill it.
ALTER TABLE wegvakken ADD COLUMN geog geography(geometry);
UPDATE wegvakken SET geog = ST_Transform(geom, 4326);
CREATE INDEX wegvakken_geog_idx ON wegvakken USING GIST (geog);
9. Now pgAdmin4 can show GEO layer with OpenStreetMap layer as base.

10. Set center point of RijksDriehoek (Onze Lieve Vrouwetoren (‘Lange Jan’) in Amersfoort.)
SELECT * FROM wegvakken ORDER BY geom <-> ‘SRID=28992;POINT(155000 463000)’::geometry LIMIT 1;

Leave a Reply

Your email address will not be published. Required fields are marked *