Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am in the process of reading shape files (provided to us by the vendor dealing with GIS products) using a C# program and loading them into a SQL database. I first read the text for the shape and then use that text to update my geometry and geography types. And I am doing this as part of a SSIS package.

My SSIS package failed on one shape file and threw me an exception:

A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.FormatException: 24305: The Polygon input is not valid because the ring does not have enough distinct points. Each ring of a polygon must contain at least three distinct points.

After running some manual updates, I found the record that was giving me grief. Then I took out parts of the multi-polygon and ran individual selects on them, creating individual polygons, eg.

SELECT geometry::STGeomFromText('POLYGON(( 118.501323586697 -20.3203577291617, 118.504216161911 -20.3220101539757, 118.502671059623 -20.3212136027048, 118.501323586697 -20.3203577291617 ))', 4326).MakeValid()

Finally, I found the polygon that was the problem shape: 'POLYGON(( 118.860739531873 -20.2274797478397, 118.860739531873 -20.2274797478397, 118.860739531873 -20.2274797478397, 118.860739531873 -20.2274797478397 ))'

I am trying to understand how to fix this so my SSIS pckage runs and translates and loads my SQL tables without me having to fix up anything manually. Any help will be greatly appreciated. Thanks

share|improve this question
    
why postgis tag? –  Nicklas Avén Jul 6 '12 at 6:52
    
It is a postgis shape file –  Divi Jul 6 '12 at 7:00
    
without me having to fix up anything manually: that seems a bad idea, there is an error in your data. You should fix this manually. –  johanvdw Jul 6 '12 at 7:13
    
The problem is that I will be running these ETL packages (on regular basis) that would read the shape files (on a location on the file system), extract the data and load into the database automatically. I can either write some sort of a script task that fixes the data or run a SQL update that somehow finds that problem polygon inside the multipolygon and removes it. That's the reason why there can't be any manual intervention. –  Divi Jul 6 '12 at 7:17
3  
shapefile is a file format from Esri. PostGIS is a spatial extension to the PostgreSQL database. It seems like you are using SQL Server from the syntax. That is another database from Microsoft that has nothing to do with PostgreSQL or the Shapefile format. –  Nicklas Avén Jul 6 '12 at 8:01

1 Answer 1

up vote 6 down vote accepted

Any ETL process is about digesting data. Somewhere along your path, you are trying to digest bad data.

So how would you write a system that tries to digest, say, a point and tries to load it into a polygon?

Sure you can write stuff to allow to digest it. If it is a point, well, then buffer it by 5 meters! bam! You have a digestable geometry without manual intervention.

But that is not the point.

Currently you are thinking of your ETL process as a binary black box for your user ("works" vs "does not work") - and you want the "does not work" to go away.

This is fundamentally a fallacy.

Think of your ETL process as a series of gates instead. Some things can pass, and some things cannot. That crap polygon you have there, most certainly came from a geoprocessing function or a topology snapping operation where the geometry collapsed onto itself because of some tolerance problem.

You don't want that in your GIS until it is fixed.

The gate should stop it, because, trust me, that polygon will cause more problems if it is let inside the rest of your GIS.

My point is that silent failures is most of the time (with some exceptions) a bad approach - even more so for ETL.

share|improve this answer
    
Thanks and I do agree that it should be fixed at source. As I am quite new to this, I was just trying to find out if someone else has seen this and if it is a common issue with these shapefiles. The other question that makes me think is that if I was to use a shapefile directly as a layer for my SSRS reports (as opposed to querying the database for the layer) how does is this bad data handled. I guess I'll just have to create a report to check it out. –  Divi Jul 7 '12 at 0:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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