Transcription of Tips and Tricks for Writing PostGIS Spatial Queries
{{id}} {{{paragraph}}}
Example: barber
Tips and Tricks for Writing PostGIS Spatial Queries
tips and Tricks for Writing PostGIS Spatial QueriesLeo Hsu and Regina ObeParagon Corporation in Action (our upcoming book!)Useful Links: PostGIS Trac and Wiki Boston GIS On Line Journal Features in PostGIS Faster Aggregates Cascaded Union (union 40,000 polygons in seconds instead of in your dreams) (need GEOS and above) Prepared Geometries for improved ST_Intersects, ST_Within, ST_Contains (need GEOS +) It is outSpeed Test 1: Polygon union2895 records unioned into 1 recordSELECT ST_Union(the_geom) FROM USMap; In PostGIS (PostgreSQL )Still chugging after 12 PostGIS (PostgreSQL )Takes 26 secsSpeed Test 2: Union and Transform2895 records unioned and transformed From NAD 83 longlat to US National Atlas Equal Area Meters into 53 recordsSELECT state, state_fips, ST_Union(ST_Transform(the_geom,2163)) As the_geomINTO statesp020 As sGROUP BY , ;In PostGIS -- Still running after 10 minutesIn PostGIS -- Takes 18 secsPostgreSQL Enhancements Windowing Functions Common Table Expressions and Recursive Common Table Expressions Unnest, array_agg More efficient query planner better results with COUNT, IN and EXISTS and INTERSECTS and EXCEPT clauses, improved Hash indexes Faster database restore PgMigrator for in place upgrade from to : Add indexes AFTER bulk insert Bulk insertINSERT INTO sometable(field1,field2.)
Tip: Keep data in form most suitable for your workload If you do mostly distance calculations and can find suitable SRID to cover your area use that.
Loading..
Tags:
Information
Domain:
Source:
Link to this page:
Please notify us if you found a problem with this document: