PostgreSQL, PostGIS, PHP. Performance of extracting geodata

July 16, 2012, 11:00 AM

Which way is the best to extract geo data from SQL: WKT or GeoJSON?

Everyone who developes geographical web-applications meets necessity to select geo-objects in PHP-application (or similar component). PostGIS provides us many functions to format geodata. Which of them is the most optimal one?

Look at these obvious queries:

JSON selection: SELECT ST_AsGeoJSON(geo_belief) AS geo FROM lightnings LIMIT [N]; Well-Known-Text selection: SELECT ST_AsText(geo_belief) AS geo FROM lightnings LIMIT [N];

SQL Selection time

12-vertex Polygon Point
Rows JSON WKT JSON WKT
10000,050,040,010,01
100000,550,440,180,06
1000005,704,280,820,74

Parsing coordinates in PHP

WKT Polygon

coordinates[] = new GeoCoordinates($longitude, $latitude); } ?>

JSON Polygon

coordinates[] = new GeoCoordinates(round($coords[0], 5), round($coords[1], 5)); } ?>

Note:

I considered only single polygons.

WKT parsing could exclude format testing and so the JSON parser in this testing had a good handicap.

PHP parsing time

12-vertex Polygon Point
RowsJSONWKTJSONWKT
10000,140,090,020,01
100002,091,740,150,10
10000043,6942,131,521,02

Notes

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in …\protected\components\geo\PolygonGeometry.php on line 27

So I had to use

ini_set('memory_limit', '-1');

And that's for WKT on 10000 rows! Of course that was garbage collector’s troubles but in fact such way is risked. Unexpected surprise! For 100000 rows my script hadn’t finished even beeing in unlimited memory environment. Only when I reduced the number of iterations to 2 I got a result. Unfortunately the same problem took place when I parsed data as GeoJSON.

Conclusion

Extracting geodata as Well Known Text (WKT) is 20-25% faster operation than selecting as GeoJSON.

Parsing WKT in PHP has a bigger difference in performance: manual parsing of WKT is 50% faster operation than built-in. It seems more correct even if my express-function is non-optimal: it uses a complicated regexp, but you can't improve built-in json_decode() function.

The both methods have potential problems with memory limits on big volumes. So one should take care about it.

The main conclusion is that built-in parsing functions (JSON) do not give obvious advantages against Perl Compatible Regular Expressions used in user-defined WKT-parsing functions. But more important is that the GeoJSON interface is more reliable and certainly universal.