PostGIS’s Simple Geospatial Features for PostgreSQL

PostGIS is a geospatial database extension of PostgreSQL. This technology can be used to find the geometry, longitude, latitude, distance and other measures. Some of its features, namely geometric types are specific to geospatial databases. These measures can be used for finding the location of an object and objects that are near to a point. Recently we have spent a lot of time talking about GIS. Several things have been examined, such as using geospatial technologies to build a custom web application here and using PostGIS for this custom National Geographic Project here.  Simple Features supported by PostGIS that will be discussed further are GeoJSON support and nearest neighbor method.

Geojson Support

The Postgis Extension has built-in GeoJSON format support using the ST_AsGeoJSON() method. This method can be used to addGeoJSON format can to be added in the sql query. The ST_AsGeoJSON() method formats a string into GeoJSON format in the sql query.

Here is an example PostgreSQL query with ST_AsGeoJSON method to format the result in GeoJSON:

SELECT ST_AsGeoJSON(geometry) as geometry
  FROM points limit 1;

And here is the GeoJSON Query Result from a query with the ST_AsGeoJSON method:

{"type":"Point","coordinates":[-76.0301621537909,43.1440190508815]}

Nearest Neighbor Method

The basis for the PostGIS geographical type is a point. Given a geometry it aims to find the x number of nearest neighbors and n geometries of data. A  query with to find the nearest objects to a geometric value has the following clause, ORDER BY geometry_column <-> geometric value LIMIT k. <-> in the ORDER BY clause is a “distance” operator that when combined with ORDER BY clause and LIMIT k allows query to find the k nearest objects to a geometric value. We will focus on using a point, so geometric value = ST_MakePoint(double x , double y), where x is longitude and y is latitude.

Here is an example PostgreSQL query with ST_MakePoint method to get the k = 5 points nearest to the given point (-110.547348214819,44.5964236630063) in the database:

SELECT ST_AsGeoJSON(geometry) as geometry
  FROM points
 ORDER BY ST_MakePoint(longitude,latitude) <-> ST_MakePoint(-110.547348214819,44.5964236630063) limit 5;

And here is the Nearest Neighbor Query Result from the query above :

{"type":"Point","coordinates":[-110.547348214819,44.5964236630063]}
{"type":"Point","coordinates":[-110.709738503157,45.0298832732411]}
{"type":"Point","coordinates":[-110.663536323832,44.0947376621328]}
{"type":"Point","coordinates":[-110.728358647307,44.0885386251708]}
{"type":"Point","coordinates":[-111.100823317053,44.6630655719391]}

PostgreSQL with PostGIS is good  geospatial database to use for a lot of applications. Some use cases are in logistics, oil and gas, and internet of things. The use cases have a little similarity because the cases are based on location. Also, with PostGIS is good for any advanced geometric based functionality.

If you are interested in discussing more about how to use PostGIS in your software applications, leave a comment, contact me here or at adetola@adelabs.com.