SQL and XPath vs RosRestra

For several years, RosReestr has been issuing data in XML format, and more recently, only in XML. And that's great! After all, this is a convenient, human-readable and machine-readable format, to work with which there is a huge amount of tools. But for some reason, Cadastre Engineers, submit data in such ancient formats as .tab and .shp - because only with these formats can they work in their GIS. To solve this problem, a bunch of self-serving and disinterested people joined in - and as a result, CI got a bunch of converters to their favorite programs. But there is a small problem - the RosRegister has a habit of changing xml-schemes , silently and without warning . As a result, the work of CI is paralyzed , because they are not able to work with XML!

Personally, I prefer to work with data in a DBMS using SQL. I will use Microsoft SQL Server and SQL Server Management Studio.

Create a table:

CREATE TABLE [dbo].[T1]( [IntCol] [int] IDENTITY(1,1) NOT NULL, [XmlCol] [xml] NULL) 

Load the data:

 INSERT INTO T1(XmlCol) SELECT * FROM OPENROWSET(BULK 'C:\work1\doc8652442.xml', SINGLE_BLOB) AS x; 

')
Get the table of coordinates:

 DECLARE @Xdoc xml; SET @Xdoc = (select [XmlCol] FROM [test1].[dbo].[T1]); WITH XMLNAMESPACES (DEFAULT 'urn://x-artefacts-rosreestr-ru/outgoing/kpt/10.0.1', 'urn://x-artefacts-rosreestr-ru/commons/complex-types/entity-spatial/5.0.1' as ns3) SELECT Parcel.value('@CadastralNumber', 'nvarchar(50)')as data, Ordinate.value('@X', 'nvarchar(50)') as X,Ordinate.value('@Y', 'nvarchar(50)') as Y FROM @Xdoc.nodes('//Parcel') col(Parcel) CROSS APPLY Parcel.nodes('//ns3:Ordinate') tab(Ordinate) 

Everything! Now we can do anything with data. For example, we want to get the geometry of the sections (Parcel). The geometry is stored in the EntitySpatial element, it can contain several contours. SpatialElement - a plot can be a polygon with holes or even a multiplicon. Let's try to get the SpatialElement as a LineString, for this we need several functions

SpatialElement => LineString

 CREATE FUNCTION [dbo].[SpatialElementToLineString](@wXml xml) RETURNS geometry AS BEGIN DECLARE @BuildString NVARCHAR(MAX); WITH XMLNAMESPACES ('urn://x-artefacts-rosreestr-ru/commons/complex-types/entity-spatial/5.0.1' as ns3) SELECT @BuildString = COALESCE(@BuildString + ',', '') + Ordinate.value('@Y', 'NVARCHAR(50)') + ' ' + Ordinate.value('@X', 'NVARCHAR(50)') FROM @wXml.nodes('//ns3:Ordinate') col(Ordinate); SET @BuildString = 'LineString(' + @BuildString + ')'; return geometry::STGeomFromText(@BuildString, 0); END 

Getting all the contours of the site:

 CREATE FUNCTION [dbo].[ParcelToLineString](@Xdoc xml) RETURNS @Tbl TABLE ( CadastralNumber nvarchar(max), Geom geometry ) AS begin WITH XMLNAMESPACES (DEFAULT 'urn://x-artefacts-rosreestr-ru/outgoing/kpt/10.0.1', 'urn://x-artefacts-rosreestr-ru/commons/complex-types/entity-spatial/5.0.1' as ns3) insert into @Tbl(CadastralNumber, Geom) SELECT @Xdoc.value('/*[1]/@CadastralNumber', 'nvarchar(max)') as CadastralNumber, [dbo].[SpatialElementToLineString](Parcel.query('.')) as geom FROM @Xdoc.nodes('//ns3:SpatialElement') col(Parcel); RETURN; end 

Now create a table to store the geometry:

 CREATE TABLE [dbo].[CadastrTbl]( [id] [int] IDENTITY(1,1) NOT NULL, [CadastralNumber] [nvarchar](255) NULL, [geom] [geometry] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

And fill it

 DECLARE @Xdoc xml; SET @Xdoc = (select [XmlCol] FROM [test1].[dbo].[T1]); DECLARE @CURSOR CURSOR SET @CURSOR = CURSOR SCROLL FOR WITH XMLNAMESPACES (DEFAULT 'urn://x-artefacts-rosreestr-ru/outgoing/kpt/10.0.1') SELECT Parcel.query('.') FROM @Xdoc.nodes('//Parcel') col(Parcel); DECLARE @Parcel xml; OPEN @CURSOR FETCH NEXT FROM @CURSOR INTO @Parcel WHILE @@FETCH_STATUS = 0 BEGIN insert into [test1].[dbo].[CadastrTbl]([CadastralNumber],[geom]) select * from dbo.ParcelToLineString(@Parcel); FETCH NEXT FROM @CURSOR INTO @Parcel END CLOSE @CURSOR 

Now we have a geometry that can be opened in a GIS ( for example, QGIS )


In QGIS, we can save our layer in any convenient format, for example in kml and see the data in GE:



Now we don’t have to wait for us to be saved, and we can take our life into our own hands! And all thanks to SQL.