SQL и XPath против РосРеестра

  • Tutorial
Уже несколько лет РосРеестр выдаёт данные в формате 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.
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 11

    0
    Отличный вывод! Теперь вам не надо ждать, когда вас спасут.
    А вот этим кадастровым инженерам надо, ибо большинство из них не владеет sql, у них нет лицензий на MsSql и доп. инструменты (которые еще нужно изучать)
    С таким же успехом они могут написать/подправить парсер xml на своем любимом языка программирования.
      0
      у них нет лицензий на MsSql

      Нет? А если найду?
      У них наверняка есть 1С и скорей всего она как раз работает на MS SQL Server
        0
        КИ инженеры просят .tab'ы потому что работают в MapInfo, вот MapInfo SQL Queries — вы хотите сказать, что КИ не владеют инструментами своей работы?
        Между прочим, MapInfo научилась хранить геометрию в MS SQL Server, раньше чем сама СУБД
        0
        Что-то я не совсем понял. То есть люди писали конвертеры напрямую в требуемые форматы, а вы создали ещё одно промежуточное решение, которое также зависимо от изменений в структуре xml?
          +2
          Я взял бесплатные Express версии и решил задачу — обработка данных. Эти древние форматы просто не нужны — вся работа прекрасно делается в СУБД на голом SQL'е
            0
            Если вдаваться в занудство, то .shp будет помоложе sql :). База хороша, но зачем разводить фанатизм, база это хранение большого кол-ва данных, а shp — разработка, различные преобразования.
              0
              вот у человека xml в 200 мегов, и таких файлов может быть несколько, а работать нужно сразу со всеми — это будет очень не спешный процесс
            0
            Ни и с Python'ом вообще сказка будет
              0
              Только Ваши запросы работать не всегда корректно будут, т.к. нужно учитывать порядок обхода контура, учитывать «бублики», многоконтурные участки. Еще есть понятие подучастков и вашим запросом Вы получите координаты по ним и все будет в одной куче, отстроить ничего не получится. также Росреестр выдает координаты как точками, так и линиями и все это нужно учитывать. Задача не такая простая, как может показаться на первый взгляд. Но в целом подход правильный, просто в реальной жизни его реализация будет более сложная.
                0
                конечно, но я хотел лишь продемонстрировать возможность и не хотел чересчур усложнять…
                0

                Хотелось бы добавить, что работу с xml в sql server можно несколько упростить двумя вещами:


                • использовать * вместо неймспейсов:


                  SELECT @Xdoc.value('/*:*[1]/@CadastralNumber', 'nvarchar(max)') as CadastralNumber, 
                  [dbo].[SpatialElementToLineString](Parcel.query('.')) as geom 
                  FROM @Xdoc.nodes('//*:SpatialElement') col(Parcel);

                • использовать латеральный джоин ( в sql server — это cross apply / outer apply)


                  select t.id, X.tag1, X.tag2 
                  from table t cross apply ( select t.xml.value('//*:tag1') tag1, 
                                                    t.xml.value('//*:tag2') tag2 ) X

                Only users with full accounts can post comments. Log in, please.