Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

PostGIS’s Simple Geospatial Features for PostgreSQL

DZone's Guide to

PostGIS’s Simple Geospatial Features for PostgreSQL

PostGIS is a geospatial database extension of PostgreSQL. This technology can be used to find geometry, longitude, latitude, distance, and other measures. Simple Features supported by PostGIS that will be discussed further are GeoJSON support and nearest neighbor method.

· Database Zone
Free Resource

Find out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate

PostGIS is a geospatial database extension of PostgreSQL. This technology can be used to find 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)
  FROM parks 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 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 the 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)
  parks
 ORDER BY geometry <-> 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 logisticsoil and gas, and internet of things. The use cases have a little similarity because the cases are based on location. Also, 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.

Align DevOps for your applications with DevOps for your SQL Server databases to increase speed of delivery and keep data safe. Discover true Database DevOps, brought to you in partnership with Redgate

Topics:
postgis ,geospatial data ,postgresql ,databases ,gis ,location

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}