Spatial Query Speed

[Former Member]

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.

[Former Member · Accepted solution]

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 🙂