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).
As I understand there are two solution of my problem:
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.