views:

758

answers:

1

PostgreSQL supports a variety of geometric types out of the box, along with lots of geometric operators and GiST indexes which seem to offer spatial indexing of data.

And then there's also PostGIS, which is an extension to PG.

What is the difference between the built-in spatial support in PG and PostGIS?

If my application needs to store geographical coordinates (points, areas, polygons) and then efficiently do queries (such as point-in-polygon, polygon intersection), do I need PostGIS or can I use the (arguably) more convenient and simpler built-in data types / syntax?

+3  A: 

First I'd like to clarify GiST indexes: GiST is actually a framework for creating indexes for new data types, not any particular indexing scheme itself. This framework happens to be used for the geometric types that come with Postgres, but it's also used for a trigram-matching text similarity index on standard text columns, and of course is used by the indexing schemes of many external packages, among which we can number PostGIS.

Whether the standard geometric data types will work for you or you need PostGIS depends entirely on your application.

PostGIS stores geometrical data in a column of type "geometry"; in this you can store more-or-less arbitrary data (points, circles, polygons, what-have-you). The indexing is fast and quite sophisticated: it can do things like lossy indexing using bounding boxes for complex shapes that are not indexable in any reasonable way otherwise. Different spatial reference systems are support, with automatic conversion of the results of queries. PostGIS also supports industry-standard OpenGIS formats, which can help with sharing data with other systems.

In contrast, the internal geometric types and index is a lot less sophisticated. There's no real "generic" geometry type; instead you have to chose to have a column's type be a point, line, circle, polygon, or what-have-you; for combinations, you will probably have to use multiple columns. The indexing is not as good; not as many different types of shapes can be indexed (though you could add bounding box support by using a separate column for them and generating the bounding boxes manually) and the indexes are probably not as fast in some situations. On the other hand, if the internal geometric types fill your needs, you gain the advantage that your application is more easily portable to other systems that have Postgres but not PostGIS installed.

My advice would be to play around with the internal geometric types and see how well that works out for you; if you start to run into issues, try out PostGIS.

Curt Sampson
thanks. hey, would you mind taking a glance at this: http://stackoverflow.com/questions/1012504/sql-query-for-point-in-polygon-using-postgres
Assaf Lavie
On that one looks like you already have your answer: you're mixing standard Postgres geometry types and PostGIS types. These are two separate things; I suggest you use one or the other, unless you find some strange case where the standard Postgres types are more efficient or work better than the PostGIS types.
Curt Sampson