none
Geometry index is too slow

    Question

  • Hi,

    I migrated my project from PostGIS to SQL spatial. My maps shows object same as before. Everything is fine until here. My field type which I show the location on the maps is Geometry. So I added a geometry index to my table. But my maps are very slow on 3-4 million row big tables.

    Before migration, I added a spatial index to PostGIS and the maps were really fast.

    Any ideas why maps are this slow?

    Many thanks

    Wednesday, January 25, 2012 10:25 AM

All replies

  • hi,

    - What are the settings for the index you created?

    - What is the execution plan for a typical query?


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, January 25, 2012 10:35 AM
    Answerer
  • Hi,

    It is a geometry grid. With BBOX values of :

    X-min : 36
    Y-min : 26
    X-max: 42
    Y-max: 45

    which are the x,y value limits for Turkey's coordinates. I am not sure if these are the right values to enter.

     

    What do you mean by saying execution plan?

     

    Wednesday, January 25, 2012 12:03 PM
  • The execution plan lets you see how SQL Server got the results of a certain query, including what indexes it used. (Just because you've created a spatial index doesn't mean that it got used). You can view the execution plan for a query in SSMS by selecting the "Include Actual Execution Plan" option.

    Try this instead: in your query, add the following line, substituting in the name of your spatial index where indicated:

    SELECT ....

    FROM YourTable WITH(index(nameofyourspatialindex))

    This will force SQL Server to use your spatial index. Does that make a difference to performance?


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, January 25, 2012 12:55 PM
    Answerer
  • I get an error message about the "with" section.

    Here's my SQL query:

     

    DECLARE @searcharea geometry = geometry::STPolyFromText('POLYGON((26.3456 36.1254, 27.21546 36.1254, 27.21546 42.87875, 26.3456 42.87875, 26.3456 36.1254))', 4326);

     

    SELECT * FROM YKN WHERE POLY.STIntersects(@searcharea) = 1 WITH(index(POLY));

     

    Error message is :

    sg 319, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'with'.

    Wednesday, January 25, 2012 2:38 PM
  • The index hint needs to come after the table name: 

    SELECT * FROM YKN WITH(index(POLY)) WHERE POLY.STIntersects(@searcharea) = 1;

     

    p.s. Is POLY the name of your index as well as your column?

     


    twitter: @alastaira blog: http://alastaira.wordpress.com/

     


    Wednesday, January 25, 2012 4:21 PM
    Answerer
  • Hi,

    Yes it is the name of both field name & index name. If this is a problem I will rename the index.

    The geometry type is POINT in this table. Does this matter about the query I send?

    Using the index had no difference. The table has 3 million 700 thousand records. While using PostGIS, I was getting the results in 1435 milliseconds. It is strange not to get any results from here. 

    Thanks

    Thursday, January 26, 2012 9:27 AM
  • On the "POLYGON" section of my Declare, which values do I have to write first? x or y values? Maybe it is because I am writing them in the wrong order.

    Thursday, January 26, 2012 10:08 AM
  • WKT always uses x - y ordering.

    It looks like you're storing geographic coordinates in the geometry datatype (which I don't recommend for a number of reasons) in which case you should state the coordinates of your POLYGON using longitude - latitude ordering.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Thursday, January 26, 2012 10:24 AM
    Answerer
  • So basicly if I don't find a way to move my data to geography type column I won't have good performance for my queries. Right?
    Thursday, January 26, 2012 2:29 PM
  • No, no, no! geometry, if anything, is faster than geography, but it's for a different sort of data. You can use the geometry datatype to store latitude/longitude data. Just as you can use the nchar(255) datatype to store a column of digits 0 and 1. But it's not necessarily the best way to store that data, and you might run into troubles when you try to perform calculations involving that data.

    The question at hand is how to get your query of a table containing geometry data to make use of a spatial index. The way to do that is to add an explicit index hint as I showed you.

    I'm unclear whether your query is just running slowly, or whether it's returning no data... adding an index should (generally speaking) not affect the results of a query.

    If your query is not returning any data then please do the following and paste the results here:

    SELECT TOP 1 POLY.STSrid FROM YKN;

    SELECT TOP 1 POLY.STAsText() FROM YKN;

     


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Thursday, January 26, 2012 3:22 PM
    Answerer
  • Hi tanoshimi,

    Result for "SELECT TOP 1 POLY.STSrid FROM YKN;" is 0.

    and result for "SELECT TOP 1 POLY.STAsText() FROM YKN;" is POINT (27.9393187241046 39.417298445051387) 

    Thanks

    Friday, January 27, 2012 8:11 AM
  • So, try:

    DECLARE @searcharea geometry = geometry::STPolyFromText('POLYGON((26.3456 36.1254, 27.21546 36.1254, 27.21546 42.87875, 26.3456 42.87875, 26.3456 36.1254))', 0);

    SELECT * FROM YKN WITH(index(POLY)) WHERE POLY.STIntersects(@searcharea) = 1;

    Friday, January 27, 2012 9:14 AM
  • Hi Alastair_2,

    Thanks for your reply. I've already tried that with the information I got from tanoshimi. But the result time of SQL Server is very slow. I don't know what to do. 

    Many thanks


    Monday, January 30, 2012 3:19 PM