Good morning,
@FlavieMoraux, I think the only alternative to doing this type of validation appears to be using Esri tooling to get the data into the database in the first place. that type of loading performs the validation. unfortunately, for me this is not an option. i imagine is is not an option for you either. after the data is in PostgreSql i can use Esri tools, but this adds a lot of overhead.
I, too, am having an issue with PostgreSql and usually this removal of repeated points works for me (st_removerepeatedpoints(geom), along with zero buffering (st_buffer(geom, 0)), and normalizing the geometry (st_normalize(geom)) - combined as follows:
st_buffer(st_normalize(st_removerepeatedpoints(geom)), 0) AS shape
unfortunately, even with this nasty conversion, Esri still complains about a 'shape integrity error' when opening the attributes of the output table. ST_IsValid returns true for every geometry in my source table.
i cant think of another solution but Esri was able to do something proprietary prior to version 10.6 (according to the referenced bug) and i can't figure out what it was. the problem i have is that my only 'solution' that works is to eliminate the rows from the selection statement that are causing me problems still, which isn't really a solution since that eliminates 20 percent of my data.
my theory, as of yet unproven, is that the nesting of rings in polyons of the features from the source (which is continually appended to the the PostgreSql table) is done in different orders (i.e. clockwise one day, and counter-clockwise the next). I would like to find a way to check the order of the digitization (the general direction that the points x,y coordinates of a polygon appear in space) and reverse it if it is not 'correct'. This gis.stackexchange discussion talks about the nested rings issue.
creating a temporary table and writing the data to a file geodatabase using the open file geodatabase api and gdal is another thing i plan on trying as well. having the data in a file geodatabase allows the 'check geometry' or 'repair geometry' tools (arcpy) to be run against the data. but this can only be done at the end of my processing stream - once the data is accessible by Esri tools. it may be that the file geodatabase also enforces some kind of validation upon loading that i am not getting with the current insert method being used.
finally, i will give an example of a geometry (converted to text using st_astext(geom)) that is generating this error. I shared this with Esri and they confirmed that by translating it back into a binary geometry, they could not load the data into the Esri platform without a shape integrity error. Despite all of the above, the following does not have an interior ring so i am totally at a loss here.
MULTIPOLYGON(((-88.7572933879595 28.8780531087591,-88.937 28.691,-89.175 28.63,-89.399 28.577,-89.712 28.693,-89.828 28.916,-90.102 28.755,-90.54 28.711,-90.5583097345133 28.711,-90.91 29.73,-91.4 30.64,-91.95 31.21,-92.53 31.44,-93.02 31.32,-93.23 30.94,-93.04 30.41,-92.9128417481541 29.3291548593095,-92.9128417481541 29.3291548593095,-93.243 29.441,-93.588 29.43,-93.777 29.35,-93.961 29.348,-94.1355988849544 29.2758706245762,-94.53 31.02,-94.17 31.67,-93.66 32.16,-93.1 32.4,-92.31 32.36,-91.67 32.04,-90.45 30.95,-88.7572933879595 28.8780531087591,-88.7572933879595 28.8780531087591)))
I'd appreciate any tips anyone on this thread has as to how to resolve this issue and whether you think there is any risk to doing the geometry manipulation described here to the validity of the data.
Thanks
Max
