Calculate WKT in Field using ArcPy

1097
9
Jump to solution
05-07-2018 06:01 AM
RobertFord1
Occasional Contributor

Thanks for any help!

I'm relatively new to ArcPy and saw that there is a way to return the WKT of a polygon via SHAPE@WKT but am unsure how to use this. 

My main goal is to obtain the WKT for each feature in a shapefile, export the shapefile table with the WKT field, then import to SQL Server 2008 R2 using STGeomFromText. 

I found this on StackExchange but am a little lost on how to implement this over multiple features within a shapefile.

Thanks!

0 Kudos
1 Solution

Accepted Solutions
RobertFord1
Occasional Contributor

Thanks guys! I figured it out!

I followed this guide where it says your SQL Server database login username and schema have to have the same name. After I created a schema with the same name as my login, I was able to use the Copy Features tool.

Now as a bonus question, do I need to have an Enterprise license to allow ArcMap to edit these SQL Server tables?

Thanks again!

View solution in original post

0 Kudos
9 Replies
JoshuaBixby
MVP Esteemed Contributor

Unless your shapes are extremely simple, your WKT representations will be truncated since shape files have a maximum text field length of 254 characters.

You should be able to load the shape file into ArcGIS Destkop, connect to SQL Server, and then copy the data over.

Outside of Esri, a quick Google search will show several approaches that will work.  For example, SQL Server 2008 R2 Map Tips: How To Import Shapefiles Into SQL Server and Aggregate Spatial Data. – ...  and OGR2OGR Patterns for SQL Server | Alastair Aitchison  .

RobertFord1
Occasional Contributor

Thanks Joshua!

I have used Shape2SQL but it doesn't seem to like shapefiles with too many features. The file I am trying to convert to SQL Server has around 40,000 features and when I try to use Shape2SQL, it seems to finish but the table is nowhere to be found in the SQL database.

I should also note that I do not have administrator rights on my machine nor is my SQL Server database enterprise enabled.

So regarding the WKT, could I calculate the WKT for each feature in a DBF table instead of a feature to get around the shapefile limit?

Thanks!

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

The *.dbf is the attribute table of the shapefile and they have the limitation.  A file geodatabase table would be best

JoshuaBixby
MVP Esteemed Contributor

Your SQL Server database doesn't have to be enterprise enabled to copy into it.  If you have create/write permissions, which it seems you do, you can connect to the SQL Server database and use tools like Copy Features to load the data from shape file into SQL Server.

RobertFord1
Occasional Contributor

Thanks again guys!

I just tried the copy features tool and it gives me error 000210. DBMS table not found, Attribute column not found. 

I checked my roles in SQL Server 2008, and I am listed as db_datareader, db_datawriter, db_ddladmin, db_owner, and public.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Which tool did you use, and what arguments did you pass to it?  Also, can you provide the whole error traceback as well?

RobertFord1
Occasional Contributor

I tried Copy Features and Feature Class to Feature Class tools. Both give the same error:

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

The 000210 error is the key, the other errors are just cascading errors from not having creating the table.  000210: Cannot create output .—Help | ArcGIS Desktop 

The output cannot be created. Potential reasons include data locking, an incorrect path, and limited access rights.

You appear to have pretty broad permissions in the database, so it is hard to troubleshoot further through the back and forth of forums. 

What happens if you connect to the workspace in ArcCatalog and use the GUI to create a new, empty table in the database?

RobertFord1
Occasional Contributor

Thanks guys! I figured it out!

I followed this guide where it says your SQL Server database login username and schema have to have the same name. After I created a schema with the same name as my login, I was able to use the Copy Features tool.

Now as a bonus question, do I need to have an Enterprise license to allow ArcMap to edit these SQL Server tables?

Thanks again!

View solution in original post

0 Kudos