Postgresql – GIS Point or discrete longitude and latitude columns

datatypespostgispostgresqlspatial

I see that the Point type is made to store geometric coordinates, however I don't see what problem it is trying to solve, or what advantages it offers over simply using two numerical columns. It's 16 bytes, so is it just an abstraction for two 8 byte floats? Or is it a string containing some kind of geohash that optimizes the performance of certain queries?

Let's say I want to get all the points for a specific viewport on a Leaflet map… with numerical columns representing latitude and longitude, I'd just filter for all the rows that have a latitude number between the N/S bounds and a longitude number between the E/W bounds, easy. I looked into using Point here, but the syntax is cumbersome and it seems to me like unnecessary overhead for development and for database performance… is it?

Best Answer

A few advantages,

  • Standardization It's standardized by the Open Geospatial Consortium (OGC)
  • Typing Functions that use a Point are said to accept a Point. So it's advantageous for typing, and working with other libraries. If for instance you need to find functions that work on a Point, search the index on Spatial Relationships and Measurements for 'point' (you can do it now in the browser).
  • Indexing while you could index a composite type: (SRID,long,lat) you'd have a very complex time defining sensible sorts and a custom r-tree index over the composite type.
  • Inclusion of SRID A point includes a Spatial reference system (SRID), without which you can't place the coordinate on Earth because you don't know what model of Earth you used.
  • Lack of independent standing Lat and Long mean nothing by themselves: what is a Lat:-5.28593 without a corresponding longitudinal coordinate. And, even together they mean nothing without an SRID.
  • N-d A Point can optionally include a third or fourth dimension. So it's always a point even in higher-dimensionality space.
    • Height? Not a problem (3dz).
    • 2d with time? Not a problem (3dm)
    • 3d and time? Not a problem. Welcome to 4d

It also provides standardized coercions to and from text use Well-known Text, and to and from binary using Well-known Binary.

As it applies to your leaflet example,

with numerical columns representing latitude and longitude, I'd just filter for all the rows that have a latitude number between the N/S bounds and a longitude number between the E/W bounds, easy.

First, you can't. One degree of longitude converges to 0 as you move towards the pole. Second, even if you could, it would be convoluted. Instead consider the GIS method,

SELECT *
FROM table
WHERE ST_DWithin( ST_Point(x,y)::geography, distance_in_meters );

Distance in meters! How nice is that. ;)

Related Question