SQL и XPath против РосРеестра07.08.2016 22:48

Уже несколько лет РосРеестр выдаёт данные в формате XML, а с недавних пор — только в XML. И это замечательно! Ведь это удобный, человек-читаемый и машино-читаемый формат, для работы с которым существует огромное количество инструментов. Но Кадастровым Инженерам почему то, подавай данные в таких древних форматах как .tab и .shp — ведь только с этими форматами они умеют работать в своих ГИС. Для решения этой проблемы подключилась куча корыстных и бескорыстных людей — и в результате КИ получили кучу конвертеров в свои любимые программы. Но есть маленькая проблема — РосРеестр имеет привычку менять xml-схемы, молча и без предупреждения. В результате работа КИ парализуется, ведь они не способны работать с XML!

Лично я предпочитаю работать с данными в СУБД, используя SQL. Я буду использовать Microsoft SQL Server и SQL Server Management Studio.

Создаём таблицу:

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

Загружаем данные:

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

Получаем таблицу координат:

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)

Всё! Теперь мы можем делать с данными что угодно. Например нам хочется получить геометрию участков (Parcel). Геометрия хранится в элементе EntitySpatial, он может содержать несколько контуров SpatialElement — участок может быть полигоном с дырками или даже мультиплигоном. Попробуем получит SpatialElement как LineString, для этого нам понадобится несколько функций

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

Получение всех контуров участка:

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

Теперь создадим таблицу для хранения геометрии:

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

И заполним её

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

Теперь у нас есть геометрия, которую можно открыть в ГИС (например QGIS)

В QGIS мы можем сохранить наш слой в любом удобном формате, например в kml и посмотреть данные в GE

Теперь нам не надо ждать, когда нас спасут, и мы можем взять свою жизнь в свои руки! И всё благодаря SQL.

SQL и XPath против РосРеестра07.08.2016 22:48

Комментарии (0)