On Mon, Nov 9, 2009 at 11:55 AM, Stefan Keller <[email protected]> wrote:
> 1. Loading data (SELECT * INTO...) and generating indices (CREATE
> SPATIAL INDEX...)
>
> 2. Non-spatial selection query (inc. filling table):
> SELECT * INTO query2_result FROM {dataset} t
> WHERE t.Name = n;
Don't do "SELECT INTO" as you'll muddy your query performance with
insert performance.
> 3. Spatial query (inc. filling table):
> SELECT * FROM {dataset} t INTO query3_result
> WHERE t.geom.STIntersects(@poly) = 1
I try to do these kinds of queries with a "SELECT count(*)" so that
there is no penalty converting the rows returned into some kind of
output format.
> 4. Combining two spatial functions (inc. filling table):
> SELECT * FROM {polygons} t INTO query3_result
> WHERE STArea(t.geom)>x AND t.geom.STIntersects(@poly) = 1
>
> 5. Join of a linestring and a point table (inc. filling table):
> SELECT s.* FROM {linestrings} t
> INNER JOIN {points} s ON STDistance(t.geom, s.geom) <= x INTO
> query5_result
> WHERE t.Type = ‘Autobahn’
For PostGIS, you have to use ST_DWithin() to get an equivalent query
to the STDistance query above.
Try some full table joins
select sum(length(p.geom)) from polys p join lines l on
st_contains(p.geom, l.geom) where l.type = 'Autobahn'
Mix up the typologies, do st_contains/st_intersects of poly/poly,
poly/line, poly/poing
Mix up the geometry sizes, do the above on polygon tables of counties
and of tracts
Publish all the SQL and the data tables so people can critique your
work before you start publishing numbers.
P.
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
