Algorithm to convert geography spatial index to geometry spatial index using a third parameter

Question

Does Microsoft have an algorithm that will take a longitude and latitude value plus a third value (county would be a good example if we were looking at a state, or region if we were looking at a country - more precisely, a slew of geographical points should be capable of defining a geometrical shape) and using the lng and lat that generate the geography point to create a geometry spatial index that incorporates the values listed within the points that are specific to the county or region?

Probably doable as a CLR, but I wondered, given the usefulness of such an algorithm, whether it might already be built into the SQL interface?

For example:

Given all the points in Alabama:

UPDATE allcountries set geog= geography::STGeomFromText('POINT('+convert(varchar(18),lng) +' '+Convert(varchar(18),lat)+')',4326); CREATE SPATIAL INDEX SIndx_allcountries_geog ON allcountries(geog);

Given the above index, the algorithm will create a second index called geom that uses the geog points (from the spatial index) above and cross reference it with all the outermost points for a given county in Alabama.  

Could call it something like: (CLR pseudo code - Q: What would be in AddGeomtoGeogRefCol?)

EXECUTE ST.AddGeomtoGeogRefCol '','Alabama,'geom',4326,'MULTIPOLYGON'

---

R, J

Answer 1

I have to be honest and say that I can't quite follow what you are trying to do... you're talking about converting a spatial index from geography to geometry, yet you refer to using a subset of state or region data and your code example doesn't demonstrate any index at all - just a geography column called geom? Did you simply mean to ask whether you could convert a column of data from geography to geometry?

Either way, the answer to your question is almost certainly "no" - geography and geometry are different datatypes designed for different sorts of data (and each has its own unique type of index) - there are no "conversion" functions built into SQL Server to allow conversion between them.

---

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

Answer 2

Ok, I was hoping for a much more thorough answer here.  While I will accept NO as a correct answer I believe there are far far better answers out there than just "NO".  

I have to be honest and say that I can't quite follow what you are trying to do... you're talking about converting a spatial index from geography to geometry, yet you refer to using a subset of state or region data and your code example doesn't demonstrate any index at all - just a geography column called geom?

First off, the code is there for someone to copy and create their own geometry index using longitude (lng) and latitude (lat), assuming they have a database with those values to begin with.  When you run that code, it creates the geometry index. And with that, the second part should be obvious once one creates the index (from the code) and clicks on spatial results after selecting from that table. 

Let me elaborate.  Find a database here and download.  http://download.geonames.org/export/dump/

I realize these are separate data types.  What I observe when I run the spatial results for a state (Alabama for example) is the dark shadow that looks very much like the state.  What I expect (in the answer to this question or from Microsoft itself) is an algorithm that takes the outermost points from that "dark shadow" and draws a line along the edge to create the geography index from the geometry index.  It seems VERY straight forward to me which is why I find the word NO disappointing.  I think "no not yet" would be more appropriate.

I am looking for others who have similar thoughts and perhaps have explored the creation of such an algorithm and... perhaps more ambitiously, as I can see the solution in my mind's eye, perhaps Microsoft has already created this algorithm and given your answer of NO, it is fairly obvious that I am perhaps a bit too optimistic with this expectation.

So, your answer is marked as helpful, but I don't consider it to be an answer to the question.  I seriously want something more substantial.  For this reason, [I hope you don't mind] I marked this one as un-answered..

A third point:

As the current spatial results will handle only 5000 points at a time (Has that been improved upon in the 2012 version by any chance?), it would seem most probably that one would need to work with a good set of points in order to choose the right 5000 to work with.

However, it also appears that there may be optimization going on behind the scenes.  One can choose a million points from an index and the SSMS will tell you it can use only 5000; but it seems to know which 5000 to use as, or at least in my experience, the "dark shadow" is consistently in the shape of the country, county, state, or region itself.   Generally where it weakens is when two country borders are separated by non-populated regions.  However, a geographic map (see the link above - allcountries will work nicely) will use rocks, mountains, bays, and streams etc to build that.   It seems likely to me that Microsoft has come out with this already.  This is my assumption given MS figured a way to optimize the spatial results to create the correct shape from points narrowing down from many to just 5000.  If nothing else, a common table expression using row_Number()ove(Partition by state,(or perhaps county),Country  order by state,country) and set the common table expression to pick up the first 5000 points based on a recursive call the to row number.

A final thought:  Perhaps the index would need to specify a particular grid and cells per object to properly create the CLR.  Also, I prefer to work with a stored procedure...  CLR is okay, but the stored proc or function is more easily managed.

---

R, J

Answer 3

If you didn't think I answered your question, don't mark it as the answer - I'm fine with that. :)

If you want a more thorough answer, let me try and explain more to you:

Firstly, I see you've edited your original question now to include the CREATE SPATIAL INDEX line - that was what was missing from your first post which is why I did not understand your references to indexes.

Secondly:

...the code is there for someone to copy and create their own geometry index using longitude (lng) and latitude (lat)...

No - the code you've pasted in your first post creates a geography index, not a geometry index (assuming geog is a column of the geography datatype, not the geometry datatype).

Therefore, the request in your second post to "create the geography index from the geometry index" doesn't make sense because you haven't got a geometry index in the first place. Even if you did have a geometry index the answer would still be "no". Indexes must be created based on the values in a named column of a table (the bit following CREATE SPATIAL INDEX ON tablename(columnname)). You can't just create an abstract geometry/geography index from another index - it's got to be based on an underlying column of data. SQL Server doesn't expose information about the individual index entry created for a particular geometry in an index, so you can't access it programatically and create a CLR function to convert, say from (Grid Cell Ref ABC in Index A <-> Grid Cell Ref DEF in Index B).

Thirdly:

There is still a limit of the number of items that can be displayed in the Spatial Results tab in SSMS 2012. I don't know for certain but, from practical experience, it still seems to be the same as in 2008/R2 (an absolute maximum of 5,000 elements, but frequently less than that if you're plotting complex geometries). 

I don't know of any "optimisations" that happen in deciding which geometries are plotted - as far as I'm aware it simply displays the first 5,000 results returned by the query - and any "dark shadow" that appears to represent the full extent of the underlying dataset is a happy coincidence rather than any clever algorithm.

A final thought:

What exactly is your objective here? What benefit do you believe would exist by creating a seperate index (geography or geometry - it doesn't matter) that contained only points that lay within a given county of Alabama? Perhaps if you could explain that more clearly then somebody might be able to suggest a solution that you find acceptable.

---

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

Answer 4

Part of the answer is staring me in the face   Creating a geometry index can be done in the same way that the geography index is created:

set geom=geometry::STGeomFromText('POINT(' +convert(varchar(18),lng)+' ' +Convert(varchar(18),lat)+')',4326);

and this let's me borrow from Alastair Aitchison: http://alastaira.wordpress.com/2011/07/26/determining-the-geographic-extent-of-spatial-features-in-a-sql-server-table/

WITH ENVELOPE as( SELECT Geom.STEnvelope() as envelope from Worldcities where country='COLORADO') ,CORNERS as( SELECT envelope.STPointN(1) as point from ENVELOPE UNION ALL select envelope.STPointN(3) from ENVELOPE) SELECT MIN(point.STX) as MinX ,MIN(point.STY) as MinY ,MAX(point.STX) as MaxX ,MAX(point.STY) as MaxY FROM CORNERS;

... given a slew of geographical points, SSMS "should be" capable of defining a geometrical shape.  Take the example where the number of points is fairly finite.  So to show that can be done, I selected Colorado from the AllCountries.txt download (as per link above) and use this as a good example of how simple it actually would be to create the geography to geometry index translation.  It is a base example primarily given that Colorado is mostly a retangle on the map.  So, the algorithm necessagy to create the shape file (geography) for Colorado, is defined by roughly four points.  The data below yields four points (assuming the square polygon).  I've labelled these geographical points as topright, topleft, bottomright, and bottomleft and then transferred those geographical values to the x1,y1 and x2,y2 coordinates that are needed to create a geometrical shape. 

CO US 41.0083 -102.01768

CO US 36.93669 -109.07428

And DENVER COLORADO is roughyly -104 39

declare @topleft float,@bottomleft float ,@topright float,@bottomright float select @topleft=max(lat) ,@bottomright=max(lng) from WorldCities where cc1='us' and adm1='co' group by adm1,cc1 select @topright=min(lat),@bottomleft=min(lng) from WorldCities where cc1='us' and adm1='co' group by adm1,cc1 declare @y2 decimal(20,8),@x2 decimal(20,8) ,@y1 decimal(20,8),@x1 decimal(20,8) select @y2=convert(decimal(20,8),@topleft) select @x2=convert(decimal(20,8),@bottomleft) select @y1=convert(decimal(20,8),@topright) select @x1=convert(decimal(20,8),@bottomright)) DECLARE @g geometry= 'POLYGON((' +convert(nvarchar(20),@x1)+' '+convert(nvarchar(20),@y1)+',' +convert(nvarchar(20),@x1)+' '+convert(nvarchar(20),@y2)+',' +convert(nvarchar(20),@x2)+' '+convert(nvarchar(20),@y2)+',' +convert(nvarchar(20),@x2)+' '+convert(nvarchar(20),@y1)+', ' +convert(nvarchar(20),@x1)+' '+convert(nvarchar(20),@y1)+'))' DECLARE @h geometry = 'LINESTRING ('+convert(nvarchar(20),@x1) +' '+convert(nvarchar(20),@y1)+','+convert(nvarchar(20),@x1) +' '+convert(nvarchar(20),@y2)+','+convert(nvarchar(20),@x2) +' '+convert(nvarchar(20),@y2)+','+convert(nvarchar(20),@x2) +' '+convert(nvarchar(20),@y1)+', '+convert(nvarchar(20),@x1) +' '+convert(nvarchar(20),@y1)+')' DECLARE @j geometry =(select 'POINT(' +convert(varchar(5),convert(int, lng)) +' '+convert(varchar(5),convert(int,lat))+')' from WorldCities where cc1='us' and adm1='co' and city_nd='Denver') DECLARE @t TABLE( ID INT IDENTITY(1,1) PRIMARY KEY, NAME VARCHAR(64), GEOM GEOMETRY ); --SELECT @g.STGeometryN(1).ToString() INSERT INTO @t(Name,Geom) SELECT 'COLORADO',@g.STGeometryN(1).ToString() INSERT INTO @t(Name,Geom) SELECT 'DENVER', @j.STGeometryN(1).ToString() INSERT INTO @t(Name,Geom) SELECT 'COLORADOHIGHLIGHT', @h.STGeometryN(1).ToString() SELECT ID,NAME,GEOM FROM @t UNION ALL SELECT ID,NAME,GEOM.STBuffer(.1) FROM @t WHERE GEOM.InstanceOf('Point')=1;

To finish this off, keep the scale in mind. It is going to be seven by five degrees and if you again assume it is roughly square then 5000 points translates to roughly 70 by 70 points. Next, iterate the points in the data starting at -102 and increment by 1/10 of a degree and do so 70 times. Each iteration would grab the min and max for that 1/10th of a degree and those points would be x1,x2...x70 and the y coordinates would be x1,x2... x50.  I suspect that where the latitude needs less detail and longitude needs more detail, that one would need to vary the grid size from 70 by 70 to say 60 by 83.  The vertical lines, like the x1,x2 horizontals would also require iternation. The statement would have the greatest detail given that many points.

Now using the shape above (which is a simple quad), set a line function up to connect the outermost points and draw a line. Save the shape file in binary and you have a shape. Thus, the geometry has been converted to geography. http://blog.sqlauthority.com/2010/03/30/sql-server-world-shapefile-download-and-upload-to-database-spatial-database/  

That is the algorithm I would expect to be behind the scenes.

R, J