49

PostgreSQL already supports spatial data types, operators and indexing.

What does PostGIS provide exactly that made it necessary to exist as an extension to PostgreSQL?

Why don't we all just use PostgreSQL's spatial functionality?

  • 2
    It is PostGIS that provides those spatial data types, operators, and indexing... – DPSSpatial Jan 6 '17 at 0:18
  • 5
    No, he's talking about the native PostgreSQL geometry types. – Evan Carroll Jan 6 '17 at 0:21
  • 4
    The short answer is that PostGIS is (now) 10x as functional as the PgSQL types. The long answer, which encompasses the question "why develop new types, and not just improve the ones already there" is addressed below. – Paul Ramsey Jan 6 '17 at 13:18
  • 1
    Same thing happened with the Java Spring framework. Java had flaws/missing features. Spring fixed many Java flaws and added useful features. Java copied Spring's fixes + features. People then ask why Spring exists... – Neil McGuigan Jan 6 '17 at 21:36
86

If you re-wound the universe to early 2001, and not only let the inventors of PostGIS see the future, but also let the PSC of PgSQL see the future, perhaps PostGIS would be a series of patches on PgSQL. But at a minimum, if we had started as patches to core, the first thing we would have run up against is:

  • core PgSQL areas don't support holes, but the GIS model really wants holes, can we change that?

And core PgSQL would have said: "no, of course not, areas have an existing well-understood semantic and we can't go making backwards incompatible changes like that".

As non-core developers, PostGIS was able to knock out monthly and 6-monthly releases for a number of years while PgSQL core plodded along with annual and longer releases. We were also able to add whatever features we wanted, whenever, since we had commit rights in our project, but gaining commit rights in PgSQL takes a Very Long Time.

By the time PostGIS was demonstrating enough external value that PgSQL core looked over and said to themselves "huh, that would have been nice to have in core as an extra feature", there was already so much code of such a different standard and style from PgSQL (not to mention under an incompatible license) that the idea of merging was not really possible.

Instead, PostGIS has become the canonical example of a Really Large Complex Extension that helps PgSQL remain modular and extensible. "How will this effect something like PostGIS" is a question often asked as core PgSQL evaluates some change. This is also a good thing, not perhaps quite as nice as PostGIS being part of core, but good enough.

There's other reasons, like the long list of dependencies PgSQL core would have hated to see, the generally lower code consistency and API cleanliness which they would have despaired of improving, and on and on. Even at conception, PostGIS was too big a hairball for PgSQL to swallow in one bite.

  • Also... PostGIS is C++. This would be a showstopper for a PostgreSQL merge.Whether or not it should be. Dependencies would also stop it completely - GDAL? Ha! I can't even get core to agree to depend on Perl > 5.8.0. Development pace is slow even if you do have commit rights; committers don't just get to free for all shove stuff in the tree, they must go through code review, and getting big changes in takes months or years. There are code quality benefits but it sure isn't fast moving. – Craig Ringer Jan 6 '17 at 4:45
  • It's a particular problem that core Pg keeps reinventing things to avoid depending on more external libraries. Because it'd mean $ancient_unix_42 with $weird_vendor_compiler on $dead_architecture would have to support it, all the buildfarm members would need updating, etc. It's one of the issues with being mature and stable I guess. – Craig Ringer Jan 6 '17 at 4:48
  • @CraigRinger Why do you think PostGIS is C++? That is an insult :-) – Nicklas Avén Jan 6 '17 at 8:52
  • It ... isn't? I could've sworn. But sure enough it doesn't look like it. My bad. I actually like (moderate and restrained) use of C++ anyway. – Craig Ringer Jan 6 '17 at 9:37
  • 4
    For a period of time PostGIS had a few pieces of C++ in order to make a binding to GEOS. Once GEOS added its own C API, those pieces were removed and PostGIS became "pure" C. – Paul Ramsey Jan 6 '17 at 13:13
33

That's simply not true, PostgreSQL does not support Spatial data types. It supports geometric types. These are perfectly fine for some things, but they're totally separate from real world coordinate systems. Native types

Update

As for the index question, it's in the FAQ

Why aren't PostgreSQL R-Tree indexes supported?

Early versions of PostGIS used the PostgreSQL R-Tree indexes. However, PostgreSQL R-Trees have been completely discarded since version 0.6, and spatial indexing is provided with an R-Tree-over-GiST scheme.

Our tests have shown search speed for native R-Tree and GiST to be comparable. Native PostgreSQL R-Trees have two limitations which make them undesirable for use with GIS features (note that these limitations are due to the current PostgreSQL native R-Tree implementation, not the R-Tree concept in general):

  • R-Tree indexes in PostgreSQL cannot handle features which are larger than 8K in size. GiST indexes can, using the "lossy" trick of substituting the bounding box for the feature itself.

  • R-Tree indexes in PostgreSQL are not "null safe", so building an index on a geometry column which contains null geometries will fail. [GiST indexes are null-safe]

  • Can you expand on your last point - the one about GiST indexes? PostgreSQL was providing R-Tree and now is providing this through a GiST index so I am confused about that point. – Zeruno Jan 6 '17 at 0:47
  • updated with direct text from the faq. – Evan Carroll Jan 6 '17 at 0:49
  • 1
    The GiST API is a PostgreSQL thing provided by access/gist.h. You can see it in use in PostGIS here – Evan Carroll Jan 6 '17 at 2:22
  • 3
    While PostGIS does have it's own rtree-on-gist implementation it's very similar to the one used by PgSQL for its core support of graphical objects for the simple reason that originally we copied theirs. – Paul Ramsey Jan 6 '17 at 4:31
  • 1
    @Zeruno, No, modifying the rtree splitter in PgSQL won't change the behaviour of PostGIS, because we have our own, in gserialized_gist_picksplit_2d(). You'll not it's not all that different from the PgSQL one, if at all. – Paul Ramsey Jan 7 '17 at 23:35
7

PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.

SELECT superhero.name
FROM city, superhero
WHERE ST_Contains(city.geom, superhero.geom)
AND city.name = 'Gotham';

In addition to basic location awareness, PostGIS offers many features rarely found in other competing spatial databases such as Oracle Locator/Spatial and SQL Server. Refer to PostGIS Feature List for more details.

PostGIS Features list expands those capabilities as well:

PostGIS adds extra types (geometry, geography, raster and others) to the PostgreSQL database. It also adds functions, operators, and index enhancements that apply to these spatial types. These additonal functions, operators, index bindings and types augment the power of the core PostgreSQL DBMS, making it a fast, feature-plenty, and robust spatial database management system.

Feature List

The PostGIS 2+ series provides:

  • Processing and analytic functions for both vector and raster data for splicing, dicing, morphing, reclassifying, and collecting/unioning with the power of SQL raster map algebra for fine-grained raster processing
  • Spatial reprojection SQL callable functions for both vector and raster data Support for importing / exporting ESRI shapefile vector data via both commandline and GUI packaged tools and support for more formats via other 3rd-party Open Source tools
  • Packaged command-line for importing raster data from many standard formats: GeoTiff, NetCDF, PNG, JPG

  • Rendering and importing vector data support functions for standard textual formats such as KML,GML, GeoJSON,GeoHash and WKT using SQL Rendering raster data in various standard formats GeoTIFF, PNG, JPG, NetCDF, to name a few using SQL

  • Seamless raster/vector SQL callable functions for extrusion of pixel values by geometric region, running stats by region, clipping rasters by a geometry, and vectorizing rasters 3D object support, spatial index, and functions Network Topology support Packaged Tiger Loader / Geocoder/ Reverse Geocoder / utilizing US Census Tiger data

Furthermore, to the points/parts mentioned already in this post. I would add as mentioned on the PostGIS website How it works

Since PostGIS is in C, it can make use of other libraries in C and C++, and it does so liberally. PostGIS depends on:

  • GEOS for many geometry processing algorithms
  • Proj.4 for coordinate re-projection functions
  • GDAL for raster processing and format support
  • LibXML2 for XML parsing
  • JSON-C for JSON parsing
  • SFCGAL for extended 3D support and additional geoprocessing algorithms

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.