# Find duplicate vertices in SQL Server geometry line (ArcSDE)

I have a line in a ZM polyline featureclass that has a invalid geometry. My suspicion is that the line doubles back on itself somewhere, which I've found SQL Server doesn't like. Anyone know of a quick SQL method or query that could help me identify the suspect bad points that are jacking up my geometry? The string representation looks like this:

``````1835815.86 12887142.42 0 0, 1835816.72 12887142.68 170 170, 1835817.53 12887142.76 349.99 350, 1835817.52 12887142.76 559.99 560, 1835817.78 12887142.76 659.99 660, ....
``````

Also, I'm wondering if I could use a regular expression and a look ahead and/or look behind to find duplicate numbers??

Here's one way with Python. Get the linestring binary as string out of the database:

``````select shape.ToString() from table_in_sde
``````

then take that and put it into a variable in python, use some regex, list and dictionary goodness to find the dups (to be honest, I Googled to find the dictionary dup stuff):

``````>>> import re
>>> s = 'LINESTRING (1835815.86 12887142.42 0 0, 1835816.72 12887142.68 170 170, 1835817.53 12887142.76 349.99 350, 1835817.52 12887142.76 559.99 560,....)'
>>> l = re.findall(r'(\d+.\d{2})\s',s)

>>> icount = {}
>>> for i in l:
...     icount[i] = icount.get(i,0) + 1

>>> for key, value in icount.iteritems():
...     if value > 1:
...             dups[key] = value
...
>>> dups
{'12887142.42': 2, '12887142.76': 3, '3081.28': 2}
>>>
``````

The third dictionary item is irrelevant (it's Z values and they can be present muliple times). The first two items are duplicate Y values. Key is the coordinate, value is the count of how many times it appears in the string.

Before going down the regex route I would start with SQL Server's STIsValid() in combination with MakeValid(). If you want to check things out on the SDE side, use sdelayer -o feature_info -r invalid.

• The problem with MakeValid() is that Z amd M values are not carried through in calculations, so running MakeValid() on a ZM linestring results in a multilinestring with no Z am M values, and I have to have my ZM values. – Chad Cooper Oct 27 '10 at 17:47
• Ah, I wasn't aware of that. Is STIsValid() flagging your bad geometries? – Derek Swingley Oct 27 '10 at 17:51
• Yes, STIsValid() does flag is as invalid. – Chad Cooper Oct 27 '10 at 18:25