1

I have a big number of photos (~10 millions) in a SQL Server database with geo coordinates column which can be NULL or NOT NULL (not placed or placed on map).

Also I created a spatial index on this geo info.

Now I am trying to select all photos inside certain polygon.

There are two ways of storing photos which are not on the map:

  • If I assign NULL to geo location of all photos which are not on the map, performance of such query too slow (As I undestood, spatial index does not working with NULL columns at all).

  • If I assign POINT(0 0) to geo location of all photos which are not on the map, performance is good, except of case with this zero point POINT(0 0). Also such request returns wrong photos (they does not exist on map).

How can I overcome these problems?

Should I add column which would contains a bit for NULL or NOT NULL and create index from two columns (this column and geo info)?

UPDATE I tried to create index from two columns, but it is impossible, because spatial index contains only one column with geo info (MSDN).

Improve this question

2 Answers 2

Reset to default
3

What version of SQL server are you using? There is a know issue with nullable spatial columns in 2008 and 2008 R2 Please look at my question on this same topic: SQL Server 2008 Performance on nullable geography column with spatial index

I solved this issue by having a separate table to store the spatial datatype. In my project I hava a Address table with an Id column, a Latitude and a Longitude Column Then I have a AddressCoordinate table that has a foreign key constraint on the Id of Address, and a Geography column. Finally I have a script to populate missing valid AddressCoordinates on a nighly basis.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I agree that it is not an ideal solution, but it does have it's upsides: 1: No dummy data inserted that needs to be filtered out later. 2: Size and performance: Dummy data will also significantly increase the index size, and thus can influence the execution time. 3: I only had the spatial data type stored on that table, so the only duplication was of the PK. This trade off made it worth my while, but I understand you came to a different conclusion for your case.
0

As I understand there are two solution of my problem:

  • Moving of zero point POINT(0 0) to far north point POINT(0 90). This solution is not ideal, but working and does not require change of SQL Server version.
  • Changing SQL Server version to 110 (in this version bug with NULL is fixed) by executing of this script: ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 110
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.