CODEWITHSUNDEEP
sql-servergeospatial
Andrew Richardson
Andrew Richardson

Reputation: 3

STPointFromText without providing co-ordinates in the code

I am trying to create a stored procedure in MSSMS that when executed will insert the data given in the execute statement into previously made tables.

This is what I currently have;

Create Procedure UDP_INSERT_TABLES3
          @shape_type varchar(30)
         ,@wktdata varchar(max)
         ,@label varchar(40)

    AS
    Begin


        set nocount on;

        if @shape_type = 'Point'
        insert into Points (Point, Label)
        values (@wktdata, @label)
END

However if I try to execute this with a statement like;

exec UDP_INSERT_TABLES3 'Point', '(179.59 -40.35)', 'My House'

I get "Error converting data type varchar to geography."

From what I understand, "STPointFromText" is used to convert varchar to Geography but every example I see has the co-ordinates 'hard coded' in so to speak.

For example;

DECLARE @g geography;
SET @g = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.ToString();

What do I have to do to get it to convert the co-ordinates given in my execute statement? I have thought about using CAST but I am unsure how to cast both latitude and longitude in the one set of brackets instead of casting them individually.

Any help would be greatly appreciated.

Upvotes: 0

Views: 1584

Answers (2)

Ben Thul
Ben Thul

Reputation: 32737

If you're not married to using something that looks like WKT but isn't, might I suggest the Point() static method? It takes three arguments (latitude, longitude, and SRID) and returns a geography or geometry point object. Like so:

declare @lat float = 50, @long float = 50;
select geography::Point(@lat, @long, 4326);

Upvotes: 1

Steve Ford
Steve Ford

Reputation: 7763

You were nearly there, you have to remember that you have to convert the input into a geography field. Try this:

CREATE Procedure UDP_INSERT_TABLES3
          @shape_type varchar(30)
         ,@wktdata varchar(max)
         ,@label varchar(40)

AS
BEGIN
   set nocount on;
   if @shape_type = 'Point'
   BEGIN
     insert into Points (Point, Label)
     values (geography::STGeomFromText('POINT' + @wktdata, 4326), @label)
   END
END

Then these statements:

exec UDP_INSERT_TABLES3 'Point', '(179.59 -40.35)', 'My House'

SELECT Point, Label, Point.ToString() as PointString FROM POINTS

Give:

Point                                           Label       PointString
0xE6100000010CCDCCCCCCCC2C44C07B14AE47E1726640  My House    POINT (179.59 -40.35)

Upvotes: 0

Related Questions