How to use WKT?

Question

I am doing a database assignment (TSQL), and have been given some "Well known Text" to work with.

I have made the tables:

drop table geometry_polygons;
go
drop table geometry_points;
go
drop table geometry_linestrings;
go 
create table geometry_polygons(
    id int primary key NOT NULL,
    polygon geometry,
    label varchar(50),
    area decimal(10,4),
);
create table geometry_points(
    id int primary key NOT NULL,
    point geometry,
    label varchar(501),
);
create table geometry_linestrings(
    id int primary key NOT NULL,
    linestring geometry,
    label varchar(50),
    length decimal(10,4),
);

How Do i insert the geometry points into the tables?

I have this to work with;

(POLYGON((175.0305935740471 -39.924665194652604,175.03033608198166 -39.924387504970255,175.0301563739777 -39.92449035313209,175.03041118383408 -39.92474952981466,175.0305935740471 -39.924665194652604)),
POLYGON((175.02037167549133 -39.91957403819063,175.01922369003293 -39.92019116822632,175.02052187919617 -39.921170336469736,175.02149283885956 -39.920446247016564,175.02037167549133 -39.91957403819063)),
LINESTRING(175.03047287464142 -39.92471250463434,175.03052115440366 -39.9247659854439,175.03007590770721 -39.924840035726646,175.02978086471558 -39.92509098331137,175.02977550029755 -39.92522262757816,175.03003299236298 -39.92538306868607,175.03039240837097 -39.92549002921579,175.031116604805 -39.92600426019882,175.0300168991089 -39.92684347686768,175.0209134817123 -39.919899060702676,175.02084910869598 -39.91996488785901),POINT(175.031116604805 -39.92600426019882),POINT(175.0300168991089 -39.92684347686768),POINT(175.02427697181705 -39.92248272908731),
POINT(175.02696990966797 -39.92459731505649),
POINT(175.0277853012085 -39.925181488771976),
POINT(175.02287685871124 -39.92140689812598),
POINT(175.02042531967163 -39.920339278607294),
POINT(175.03031730651853 -39.924552061955026))

score 2 · Answer 1 · edited May 23 '17 at 12:19

2

You can convert WKT as Sql Server Geometry using STGeomFromText method http://msdn.microsoft.com/en-us/library/bb933823.aspx

As your next question might be "what's this SRID stuff, then", here's related SO question Geometry column: STGeomFromText and SRID (what is an SRID?)

edited May 23 '17 at 12:19

Community

1
1

answered Jun 19 '14 at 21:19

Mikko Viitala

8,344
4
37
62

John Powell · Accepted Answer · 2014-06-19T21:25:39.983

0

Something like this:

declare @g geometry; 
set @g=geometry::STGeomFromText('POLYGON((175.0305935740471 -39.924665194652604,175.03033608198166 -39.924387504970255,175.0301563739777 -39.92449035313209,175.03041118383408 -39.92474952981466,175.0305935740471 -39.924665194652604))', 4326);  
insert into geometry_polygons (polygon) values (@g);

Note you have to specify a coordinate reference system, sometimes called, spatial reference system (SRS), for example, 4326, which means lat/long, for many spatial functions to work properly.

EDIT: the geometries which you have added in your updated question, are a mixture of Polygons, Linestrings and Points, so, you will need to use a GeometryCollection (which can take a mixture of types) if you want to add them all in one go, or split them into separate entries. It is generally a bad idea, though, to mix different geometry types like this, as many spatial functions don't work on GeometryCollections.

edited Jun 19 '14 at 21:25

answered Jun 19 '14 at 21:19

John Powell

12,253
6
59
67

So Something Like This `declare @g geometry; set @g=geometry::STGeomFromText('POLYGON((175.0305935740471 -39.924665194652604,175.03033608198166 -39.924387504970255,175.0301563739777 -39.92449035313209,175.03041118383408 -39.92474952981466,175.0305935740471 -39.924665194652604))', 4326); insert into geometry_polygons (polygon) values (@g); ` – mcclma Jun 19 '14 at 21:23
i have, but i get "Cannot insert the value NULL into column 'id', table 'master.dbo.geometry_polygons'; column does not allow nulls. INSERT fails." – mcclma Jun 19 '14 at 21:31
Yes, well, you have to provide a value for id, as the error states. Your question asked how to insert a geometry column, which I have answered, but I will leave it to you to figure out how to add the rest of the required values to the insert. – John Powell Jun 19 '14 at 21:33
You are welcome. I know spatial in SQL Server is a bit hard to get one's head round. In general, though, you should make some effort to figure it out yourself before coming on SO, ie, I have tried this, it doesn't work. – John Powell Jun 19 '14 at 21:38

How to use WKT?

2 Answers2