2

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?

  • I believe the title exit no longer reflects the question. PostgreSQL geometric data types are different from those you have when using PostGIS. – Philip Tinney Nov 9 '17 at 0:06
3

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. ;)

  • If the data is coming from Google Maps API, the inaccuracy from Google is inaccurate in the same way when given to Leaflet, so they cancel out and net result is that it should work without an SRID, right? The viewport gives two lat/lng pairs and so it should work with the data they give if I understand correctly. – dvtan Nov 8 '17 at 23:36
  • What do you mean inaccuracy from Google? – Evan Carroll Nov 8 '17 at 23:42
  • 1
    I think there is some confusion here. Google tends to give lat/long on EPSG:3857. It's not inaccurate (well, it is but that's just becaues it does so much). You need to keep all of your math on that projection of EPSG:3857. If you do, you can within reason get useful calculation. But longitude is going to be relative to the placement of the point on that projection. One degree of longitude will never be a measure of distance. Just look at a toy glove. Your pinky can touch all the lines in the right spot -- on Earth too. – Evan Carroll Nov 8 '17 at 23:45
  • 1
    @dtgq most certainly. =) You need to be doing the calculations in whatever they're sending out, not the projection for the web interface. – Evan Carroll Nov 9 '17 at 0:27
  • 1
    @dtgq but that not the question here, the second this involves an api and not the DB it's on-topic on Stack Overflow but not here. – Evan Carroll Nov 9 '17 at 0:28
3

Points are useful for spatial relationships. You can determine what points are within polygons (addresses within a city boundary, as a city is rarely a simple square), what point is closest to another point (bakeries near your home), etc. Using Lat/Long you can calculate some of this but there are times that Lat/Long will get the answer very very wrong (http://workshops.boundlessgeo.com/postgis-intro/geography.html).

A great intro to geometry in Postgres: http://workshops.boundlessgeo.com/postgis-intro/

Another way of looking at it: Looking at a map of the United States how would you quickly determine if a point is within the country? By using min/max lat/long you would have to include AK and HI (and maybe Puerto Rico, Guam, etc) and the min/max box of lat/long suddenly won't work because it overlaps Canada/Mexico/etc. But using a point you can then check via ST_Within() in a quick geometry check.

1

The advantage to using a PostgreSQL geometric data types is that you can use geometric functions and operators. Your example is fairly straightforward and a basic query that works is fairly simple. Using the geometric operator, you can do a query that returns only lines that intersect a path or are contained within a circle. Without those operators you will have to return more rows and do the check in code. That said, the geometric operators are not GIS related, so aren't appropriate for mapping. Use PostGIS if you need to do mapping related queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.