10

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??

2 Answers 2

5

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.

3

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.

3
  • 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. Oct 27, 2010 at 17:47
  • Ah, I wasn't aware of that. Is STIsValid() flagging your bad geometries? Oct 27, 2010 at 17:51
  • Yes, STIsValid() does flag is as invalid. Oct 27, 2010 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.