I am in the process of reading shapefiles (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 shapefile 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, e.g.

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 package runs and translates and loads my SQL tables without me having to fix up anything manually. Any help will be greatly appreciated.

  • why postgis tag? Jul 6, 2012 at 6:52
  • It is a postgis shape file
    – Divi
    Jul 6, 2012 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, 2012 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, 2012 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. Jul 6, 2012 at 8:01

1 Answer 1


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.

  • 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, 2012 at 0:31

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.