Thursday, December 10, 2009

Extra, extra, PostGIS Really Fast!

Because, frankly, I love nothing more than approbation, I am going to quote this comment on "Much Faster Unions in PostGIS" in full:
This is a truly spectacular piece of work. We have often been asked by clients to buffer and merge point datasets with several million points. We attempted this using ArcWhatever (could barely open the points, let along buffer them) and FME, which ran for a week and then gave an out of memory error. So, I do the whole configure, make, make install thing, 4 times, for postgres, goes, proj4 and postgis. After a lot of swearing and running ldconfig a few million times I eventually get postgis to accept that geos really is installed -- MySQL might have more limited spatial functionality, but it sure is a lot easier to build from source. Anyway, I digress. I run a few random queries using the excellent generate series capability in postgres, and manage to create, buffer and merge 100,000 points in a few seconds. Finally, I try this on a real world dataset, namely all of the postal addresses in Wales, 1.4 million or so. With a 200m buffer, this ran on a reasonably pokey 64-bit linux box in 19 minutes. Truly astonishing. Well done. Much as I love MySQL, this was a bit of St. Paul on the road to Damascus moment.
Full credit to Martin Davis, who implemented this technique in JTS. We just borrowed it for database land.
 

3 comments:

Regina Obe said...

Paul,

Surely you jest. You love MySQL? How long ago was that?

I may see the beauty in MySQL and in some cases at least appreciate what others see in it, but I don't recall you ever saying one kind word about MySQL at least not in the past 5 years. I fear the sky may be falling or pigs will be flying soon :).

Paul Ramsey said...

Regina, it's a comment left by an impressed MySQL user on the PostGIS Union blog post. Hence the block quoting.

Regina Obe said...

I guess my Rss reader folded everything together. I was wondering why you were quoting yourself :)

Oh well No pigs flying today.

About Me

My Photo
Victoria, British Columbia, Canada

Followers

Blog Archive

Labels

bc (37) it (29) postgis (20) icm (11) video (11) enterprise IT (10) sprint (9) open source (8) osgeo (8) cio (6) foippa (6) gis (6) management (6) spatial it (6) enterprise (5) foi (5) foss4g (5) mapserver (4) outsourcing (4) politics (4) bcesis (3) oracle (3) COTS (2) architecture (2) boundless (2) esri (2) idm (2) natural resources (2) ogc (2) open data (2) opengeo (2) openstudent (2) postgresql (2) rant (2) technology (2) vendor (2) web (2) 1.4.0 (1) HR (1) access to information (1) accounting (1) agile (1) aspen (1) benchmark (1) buffer (1) build vs buy (1) business (1) business process (1) c (1) cathedral (1) cloud (1) code (1) common sense (1) consulting (1) contracting (1) core review (1) crm (1) crockofshit (1) cunit (1) custom (1) data warehouse (1) deloitte (1) design (1) development (1) digital (1) email (1) environment (1) essentials (1) evil (1) exadata (1) fcuk (1) fgdb (1) fme (1) foocamp (1) foss4g2007 (1) ftp (1) gds (1) geocortex (1) geometry (1) geoserver (1) google (1) google earth (1) government (1) grass (1) hp (1) iaas (1) icio (1) industry (1) innovation (1) integrated case management (1) introversion (1) iso (1) isss (1) isvalid (1) javascript (1) jts (1) lawyers (1) mapping (1) mcfd (1) microsoft (1) mysql (1) new it (1) nosql (1) opengis (1) openlayers (1) oss (1) paas (1) pirates (1) policy (1) portal (1) proprietary software (1) qgis (1) rdbms (1) recursion (1) redistribution (1) regression (1) rfc (1) right to information (1) saas (1) salesforce (1) sardonic (1) seibel (1) sermon (1) siebel (1) snark (1) spatial (1) standards (1) svr (1) taxi (1) tempest (1) texas (1) tired (1) transit (1) twitter (1) uber (1) udig (1) uk (1) uk gds (1) verbal culture (1) victoria (1) waterfall (1) wfs (1) where (1) with recursive (1) wkb (1)