topic Re: Calculate WKT in Field using ArcPy in Python Questions

Calculate WKT in Field using ArcPy

RobertFord1 — Mon, 07 May 2018 13:01:31 GMT

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!

Re: Calculate WKT in Field using ArcPy

JoshuaBixby — Mon, 07 May 2018 14:07:40 GMT

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. – Boon Blog and OGR2OGR Patterns for SQL Server | Alastair Aitchison .

Re: Calculate WKT in Field using ArcPy

RobertFord1 — Mon, 07 May 2018 14:49:42 GMT

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!

Re: Calculate WKT in Field using ArcPy

DanPatterson_Retired — Mon, 07 May 2018 15:11:08 GMT

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

Re: Calculate WKT in Field using ArcPy

JoshuaBixby — Mon, 07 May 2018 15:37:50 GMT

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.

Re: Calculate WKT in Field using ArcPy

RobertFord1 — Mon, 07 May 2018 16:44:26 GMT

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.

Re: Calculate WKT in Field using ArcPy

JoshuaBixby — Mon, 07 May 2018 19:13:45 GMT

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

Re: Calculate WKT in Field using ArcPy

RobertFord1 — Tue, 08 May 2018 11:05:58 GMT

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

Re: Calculate WKT in Field using ArcPy

JoshuaBixby — Tue, 08 May 2018 13:45:22 GMT

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?

Re: Calculate WKT in Field using ArcPy

RobertFord1 — Wed, 09 May 2018 11:39:39 GMT

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!