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;

11. select ST_X(geom), ST_Y(geom) from shapefile

WAMP + PostgreSQL integration

If you want to extend an existing Windows WAMP installation with a PostgreSQL database execute the following steps:

Apache 2.2.X configuration
Add the next line to the Apache 2.2.x httpd.conf configuration:
LoadFile “C:/Program Files/PostgreSQL/9.1/bin/libpq.dll”

Above line must be entered before the next line.
LoadModule php5_module “c:/wamp/bin/php/php5.2.11/php5apache2_2.dll”

PHP 5.2.X Configuration
Enable (remove trailing ; character) the following two lines in the php.ini configuration file.

Restart WAMP
Restart the wamp (apache, php and mysql) software.

Create the following test.php file and place it in the apache www root directory:

echo extension_loaded('pgsql') ? 'yes':'no';

When the php file is executed ( in apache the following information must be showed on screen.