Is there a point limit to Well Known Text Polygons?

[dan]

I've got a watershed boundary that is ~10000 points. In the SQL query
I've been using up until now, I've just been constructing a well known
text polygon. I'm using a python script that constructs my SQL query
then queries the database. Is there an alternative for large point
count geometries?


Dan

[a.furieri]

Hi Dan,

you can pass a geometry value in two different ways:

1) passing some "well known" expression to the the appropriate
SQL function: WKT, WKB, EWKT, GeoJSON, GML and so on
Obviously, when the geometry contains many thousand points,
the corresponding "well known expression" will be really huge.

2) in alternative you can use the C API internal methods supporting
direct access to geometry objects; but this requires using C or C+
+

bye Sandro

[dan]

Sandro,
Thanks for the reply. Here's a followup question, which may be a
general spatial query question. I am query multiple days of radar
data, basically importing an hourly file, querying for the area
intersecting, then moving to the next file. Would I see performance
improvements if I stored my polygon in a virtual table as a well known
binary instead of passing it in as a Well Known Text?
My query is as follows(forgive the formatting):
SELECT * FROM(
SELECT
(ST_Area(ST_Intersection(radar.geom,GeomFromText('%s')))/
ST_Area(GeomFromText('%s'))) as percent,
radar.precipitation as precipitation
FROM precipitation_radar radar
WHERE radar.collection_date >= '%s' AND
radar.collection_date <= '%s' AND
Intersects(radar.geom, GeomFromText('%s')))

I'm trying to improve the ~30 second query time as I'm doing this over
14 days with each day having hourly files.

Thanks again!

[a.fu...@lqt.it]

On Fri, 10 Feb 2012 05:20:04 -0800 (PST), dan wrote

> Would I see performance improvements if I stored my polygon
> in a virtual table as a well known binary instead of passing
> it in as a Well Known Text?
>

I surely foresee a stong performance improvement
avoiding at all to use any virtual table.
Virtual Tables are intrinsically slow: and converting
from WKT (or WKB) to internal geometry is a second
slow process ... it's no good :-P

Directly feeding a "real" geometry table is surely
better and faster.
Not to say that creating a "real" geometry table you
can then create a Spatial Index
and the Spatial Index will presumably allow to speed
up your query in a dramatic way.

bye Sandro

[dan]

Sandro,

Thanks for the reply. A spatial index is not going to help me in this
case, I use a bounding box to limit the data to an area just large
enough to cover my area of interest.
So you're saying that if I created a real table and stored my polygon
in a POLYGON geometry column that would give me some speed
improvements?

Thanks!

Dan

[a.fu...@lqt.it]

On Fri, 10 Feb 2012 05:51:07 -0800 (PST), dan wrote

> So you're saying that if I created a real table and stored my polygon
> in a POLYGON geometry column that would give me some speed
> improvements?
>

yes

[dan]

Excellent, thanks alot!