PostGIS to GeoJSON

SELECT
    row_to_json(feature_collection)
FROM (
    SELECT
        'FeatureCollection'::text as type,
        case
            when
                array_agg(feature_collection_data) is null
            then
                array_to_json(ARRAY[]::varchar[]) 
            else
                array_to_json(array_agg(feature_collection_data))
        end as features
    FROM (
        SELECT
            'Feature'::text as type,
            ST_AsGeoJSON({geomCol})::json as geometry,
            row_to_json((SELECT row FROM (SELECT {properties}) as row)) as properties
        FROM
            {table} as row_data
        WHERE
            {where}
    )  as feature_collection_data
)  as feature_collection;";

Notes:

{geomCol} - geom column to select from or geometry returning call such as ST_GeomFromText('POINT(' || lon || ' ' || lat || ')' ,4326)

{properties} - a comma separated list of columns to select from; can be aliased

{table} - a table to select from

{where} - a where clause if needed

Installing pgsql as a aservice on Windows

Get the latest pgsql from here and if you need postgis from here.

Unpack the pgsql zip to a desired destination.

Create a directory for the db files.

Navigate to the bin folder of pgsql and run the following command: 

initdb -U postgres -E UTF-8 -A md5 -D <path to data directory> -W

When prompted, provide the master pass. Your console should look like this:


Now it's time to adjust the db cfg. You may find it in the db files folder, it is called "postgresql.conf". Make sure you adjust the port the db listens on and also that it is available for localhost:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = 'localhost'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)
#port = 5432				# (change requires restart)

By default pgsql listens on 5432. If you already have another pgsql live or need to change it for whatever reason, simply adjust the port value.

Once this is ready, it's time to create a service for our db (you may need to run the console as an admin though):

pg_ctl register -N ServiceName -U postgres -P <password> -D <path to data directory> -w

The console should look like this:


And the service should be visible in the service manager:


If you wish to rename the service then simply delete it and register again:

sc delete ServiceName

The final step is to adjust the service properties, such as a user that runs the service (by default it runs as NetworkService) or the startup mode:



Now, if you also need postgis, simply use the exe installer and you're good to go.

Access pgsql x64 from manifold x32/x64 - both installed on win 64

Scenario:

  • windows server x64
  • manifold 8.x x64
  • pgsql 9.x x64
In order to allow manifold x64 to connect to the db server one needs to:
  • install Microsoft Visual C++ 2013 Redistributable Package x64 (actually pgsql will not even start without it)
  • add pgsqls bin to the PATH variable or
  • copy intl.dll, libeay32.dll, libiconv.dll, libpq.dll, ssleay32.dll to manifold x64 installation folder
All good to go until one needs to connect to pgsql from manifold x32... In order to do that:
  • install Microsoft Visual C++ 2013 Redistributable Package x32 and
  • copy intl.dll, libeay32.dll, libiconv.dll, libpq.dll, ssleay32.dll to manifold x32 installation folder
Now you should be able to connect to your x64 pgsql from both manifold versions

Spatial index in postgis

--create geom index
CREATE INDEX mytable_geom_gist
  ON public.mytable
  USING GIST (geometry);

--vacuum analyze
VACUUM ANALYZE public.mytable;