Sample Insert, Update SQL for polygons and points

Topics: Developer Forum, User Forum
Apr 11, 2007 at 7:43 AM
I have just started trialling MSSQLSPATIAL (so far so good) and was wondering if there are any examples of INSERTS and UPDATES SQL statements for tables after they have been created. For example
1. SHP file imported successfully (Point data)(DONE)
2. Performing point in polygon query using SQL query - returning expected results (DONE)
3. Wanting to INSERT a new point into table (TO BE DONE)
4. Wanting to UPDATE existing points (TO BE DONE)
Any guidance would be appreciated.
Apr 11, 2007 at 5:06 PM
Sample code for UPDATE:
command.CommandText = "UPDATE the_table SET the_geom = ST.GeomFromText('POINT(' + cast(@latitude as nvarchar) + ' ' + cast(@longitude as nvarchar) + ')', @srid) WHERE oid = @oid";
command.Parameters.AddWithValue("@latitude", -33.414266);
command.Parameters.AddWithValue("@longitude", -70.60338);
command.Parameters.AddWithValue("@srid", -1);
command.Parameters.AddWithValue("@oid", 1234);

Best regards,
Ricardo Stuven.
Apr 12, 2007 at 12:21 AM
Thanks Ricardo. I appreciate the quick response.

One item I forgot to ask was how do you INSERT a POLYGON from a string of lat/longs e.g. 145.128063 -38.438871,145.070315 -38.555053,145.276559 -38.592864

AND then once it is in the BINARY object how do you query the record and EXTRACT the coordinates out again as we need to do this to display in an application based upon LAT/LONGs

1. Insert polygon based upon lat/longs into geometry field
2. Query the table and extract this back out again using a SELECT query.

Apr 12, 2007 at 1:34 AM
I have been able to perform the INSERTS and UPDATES successfully (see below) but I am still unclear how to extract the original lat/longs from the record once it has been stored in the binary?? Any help would be appreciated.

UPDATE address SET geometry = ST.GeomFromText('POINT(149.752422772739 -37.5482896543924)', 4202) WHERE oid = 2

insert into cities
(506,'Jasville',ST.GeomFromText('POINT(149.752422772739 -37.5482896543924)', -1))

insert into poly_table
('NewPoly',ST.GeomFromText('POLYGON((140.79062049517577 -33.78544996952783, 140.77690503627173 -38.46242708636113, 143.4514195225643 -39.148200857157796, 146.22194222118531 -39.408794890060534, 149.82910791295424 -38.18811757804246, 150.46001902254122 -37.282896200590855, 148.11467554994618 -35.69190105234257, 146.13964946776093 -34.45750826490857, 142.73821565955293 -33.75801901869597, 141.11979150887333 -33.7305880678641, 140.79062049517577 -33.78544996952783))',-1))

Apr 16, 2007 at 3:11 PM
For simple geometries, such as points, you could extract the coordinates you need using functions. For example:
SELECT ST.X(point), ST.Y(point) FROM geomtable WHERE oid = 1
SELECT ST.StartPoint(line), ST.EndPoint(line) FROM geomtable WHERE oid = 2

But if you want to extract all the coordinates of, say, a polygon, you may:
1) Use ST.AsText and parse well-known text (relatively easy, but not recommended)
2) Use ST.AsBinary and parse it using an existent library such as NetTopologySuite or SharpMap (recommended).

For example, see the discussion on using MsSqlSpatial with ArcObjects Engine.

Best regards,
Ricardo Stuven.
Apr 18, 2007 at 3:35 AM
Thanks Ricardo for the feedback.

I have just tried the ST.AsText and it is retruning exactly what I need - but I am curious to see why this is not recommended)

select ST.asText(geometry) from parcels where oid=1
Apr 19, 2007 at 9:26 PM
I recommended the second approach because 1) ST.AsBinary returns a more compact value and 2) you don't have to parse it by yourself (error prone), rather you rely on tested code.

Best regards,
Ricardo Stuven.
Apr 19, 2007 at 11:42 PM
Thanks for all your help Ricardo. I really appreciate the fast response provided.