We are using the latest version of Sybase 12 and I have been playing around with the spatial query functionality. It is quite impressive. I have loaded up a shapefile containing 500,000 linestrings and now I am beginning to make queries against it using the Intersects method. I am trying to determine if any of the linestrings intersect a 4 point polygon (basically a box) I am feeding it via a cursor query. I am developing this in C using the ESQL interface. My approach is to set up a query and if I process 1 row based on the select, I know I have a hit. My cursor query looks like:
EXEC SQL DECLARE fiber_cur CURSOR FOR SELECT LAYER FROM IN_Fiber C WHERE C.geometry.ST_intersects(NEW ST_Polygon(:ticket_polygon,4326)) = 1 ;
If I can move to the first row, I know the box intersects with a linestring.
Everything works fine. With a table of 500,000 rows it takes about 3-4 seconds per check.
I have tried using the IntersectFilter method but it doesn't really speed up anything. Does anyone have any suggestions to increase the speed of this query? I have a primary key in the table of linestrings but it really is just a placeholder.
Thanks for any ideas.
asked 02 Jul '13, 10:59
Posting to my own question - I think I found an answer. I went in and created an index on the Geometry field (the linestrings) in the table - HELLO !!!!! Unbelievable speed!!!! It now processes about 10 queries a second.
Life is good :)
answered 02 Jul '13, 13:46