Handy queries and features for Sql Server geography data from OSM

 I'm using SQL for a lot of work on geographic data and here I wanted to share a few of the "not-so-obious" snippets I use when working with OpenStreetMap (OSM) data.

When providing data to be parsed as Latitude and Longitude We can use the STRING_AGG function.

select	w.Id, 
	STRING_AGG(
    	cast(n.Lat as nvarchar(max)) + ' ' + 
    	cast(n.Lon as nvarchar(max)), ', ') within group (order by wn.NodeOrder) as LatLon
from	Way w inner join WayNode wn on wn.WayId = w.Id inner join Node n on n.Id = wn.NodeId
group	by w.Id

This example is based on my working OSM data model (way - waynode -node etc.)

The list above will provide a clean list of "lat lon, lat lon ...". But when I need for instancce WKT it can ve altered by switching the "n.Lat" and "n.Lon" above.  

Of couse one can use the built-in functions such as:

select w.shape.STAsText() as wkt, w.Shape.AsGml() as gml from....

Firstly, I used the agg function to make the sql geography shapes in the first place....

And remember that the WKT provide the data in Lon/Lat (X/Y) order rather than the more widely used Lat/Lon order. and GML has no separator between the groups and mus be parsed in even/odd order. And it may need an aggregate function to join all of them into a single xml file. 

This can in a simplistic way be used to make shapes from data tables imported from OSM:

--drop view BasicOsmLines
create view BasicOsmLines as
select	Id,
		geography::Parse
		(cast(basicshapes.LineType as nvarchar(max)) + cast(basicshapes.LatLon as nvarchar(max)) + cast(basicshapes.LineTypeEnd as nvarchar(max))) as Shape
from	(
		select	w.Id, 
				case 
					when count(wn.NodeId) = 1 then 'POINT('
					when count(wn.NodeId) > 1 then 'LINESTRING('
				end as LineType,
				case 
					when count(wn.NodeId) = 1 then ')'
					when count(wn.NodeId) > 1 then ')'
				end as LineTypeEnd,
				STRING_AGG(cast(n.Lon as nvarchar(max)) + ' ' + cast(n.Lat as nvarchar(max)), ', ') within group (order by wn.NodeOrder) as LatLon
		from	Way w inner join WayNode wn on wn.WayId = w.Id inner join Node n on n.Id = wn.NodeId
		group	by w.Id
		) basicshapes

But be aware thet the view may be a bit sketchy in case the lat/lon collection produce invalid line shapes. 

By default OSM should not contain MultiPolygons or MultiGeometry or GeometryCollection in the WayNodes. But this should be saved in the "<reference><member ..." elements in the xml. Maybe I can find something on that later...

For now this query can get the shapes that should be polygons:

-- where exists( -- Alternative: where not exists

select	*
from	WayTag wt
where	(isnull(wt.TagKey, '') = 'area' and isnull(wt.TagValue, 'no') != 'no')
or		(isnull(wt.TagKey, '') = 'building' and isnull(wt.TagValue, 'no') != 'no')
or		(isnull(wt.TagKey, '') = 'leisure' and isnull(wt.TagValue, '') = 'park')

Or alternatively:

select	* 
from	BasicOsmLines
where	Shape.STGeometryType() = 'LineString' 
and	Shape.STIsValid() = 1 
and	Shape.STIsClosed() = 1

But here we may come up with errors for invalid  shapes. And to handle this we should go from using views to tables.

Then one may consider using

.EnvelopeAngle to reorient
and .MakeWalid() and .IsValid()

Maybe this is mostly for myself so I have a snippet repository for later....