locked
inside stored procedure, how to create geography point on-the-fly within openxml RRS feed

  • Question

  • Hi there -

    I'm trying to insert Lat/long data into a Geography field, on-the-fly, using serialized xml (the Geography type doesn't serialize, so i had to pass the lat/long to the stored proc as decimals).
    I can't quite get the syntax right, perhaps someone else has done this already?

    Thanks,
    Mike

    declare @idoc int
    declare @doc varchar(MAX)
    set @doc ='<Rows xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    			<Row ID="13" CentroidLat="51.644264" CentroidLon="-0.1502" Sum_Analysis="3824324" />
    		</Rows>'
    exec sp_xml_preparedocument @idoc OUTPUT, @doc
    
    Insert into dbo.test_Portfolio_Location_Results_1000
    Select *
    From openxml(@idoc, 'Rows/Row', 1)
    	With(
    		xID INT '@ID',
    		xCentroid Geography geography::Point('@CentroidLon', '@CentroidLat', 4326),
    		--xCentroidLat Numeric(18,2) '@CentroidLat' ,
    		--xCentroidLon Numeric(18,2) '@CentroidLon' ,
    		xSum_Analysis Numeric(18,2) '@Sum_Analysis'
    	)
    	


    Tuesday, February 2, 2010 5:03 PM

Answers

  • You need to retrieve each coordinate as a separate value, and then use the geography::Point() method in the SELECT statement, not in the schema declaration of the XML. Like this:

    declare @idoc int
    declare @doc varchar(MAX)
    set @doc ='<Rows xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    			<Row ID="13" CentroidLat="51.644264" CentroidLon="-0.1502" Sum_Analysis="3824324" />
    		</Rows>'
    exec sp_xml_preparedocument @idoc OUTPUT, @doc
    
    Insert into dbo.test_Portfolio_Location_Results_1000
    Select
      xID,
      geography::Point(xLat, xLon, 4326) AS xCentroid,
      xSum_Analysis
    From openxml(@idoc, 'Rows/Row', 1)
    	With(
    		xID INT '@ID',
    		xLat float '@CentroidLat',
    		xLon float '@CentroidLon',
    		xSum_Analysis Numeric(18,2) '@Sum_Analysis'
    )

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by blomm Wednesday, February 3, 2010 9:36 AM
    Tuesday, February 2, 2010 6:22 PM
    Answerer

All replies

  • Any way you could leverage GML?  Perhaps you could serialize using the AsGml() method and deserialize using the GeomFromGml method?
    Tuesday, February 2, 2010 5:44 PM
  • You need to retrieve each coordinate as a separate value, and then use the geography::Point() method in the SELECT statement, not in the schema declaration of the XML. Like this:

    declare @idoc int
    declare @doc varchar(MAX)
    set @doc ='<Rows xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    			<Row ID="13" CentroidLat="51.644264" CentroidLon="-0.1502" Sum_Analysis="3824324" />
    		</Rows>'
    exec sp_xml_preparedocument @idoc OUTPUT, @doc
    
    Insert into dbo.test_Portfolio_Location_Results_1000
    Select
      xID,
      geography::Point(xLat, xLon, 4326) AS xCentroid,
      xSum_Analysis
    From openxml(@idoc, 'Rows/Row', 1)
    	With(
    		xID INT '@ID',
    		xLat float '@CentroidLat',
    		xLon float '@CentroidLon',
    		xSum_Analysis Numeric(18,2) '@Sum_Analysis'
    )

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by blomm Wednesday, February 3, 2010 9:36 AM
    Tuesday, February 2, 2010 6:22 PM
    Answerer
  • of course! duh! thanks for the second set of eyes.
    Wednesday, February 3, 2010 9:36 AM