5

I have a .shp file which I am trying to import into a PostGIS database using the "PostGis SHP and DBF Loader". This works smoothly but some (maybe 5%) of the records have an empty geometry field. (I have double checked the original files and there should be geometry on every row). I can't see anything special about these rows - it appears random but presumably isn't.

Can anyone tell me why this might happen?

Improve this question
2
  • No error output?
    – Pablo
    Commented Feb 17, 2011 at 11:41
  • No, it just says: Shapefile type: Polygon Postgis type: MULTIPOLYGON[2] Importing shapefile (2346 records)... Creating spatial index... Shapefile import completed.
    – Mr_Chimp
    Commented Feb 17, 2011 at 11:49

2 Answers 2

Reset to default
6

Just to be completely sure, if you are using pgAdmin to have a look at the resulting table, be careful because sometimes the geometry fields appear to be empty when in fact they aren't. This is probably due to pgAdmin having trouble showing big geometries.

You can check the geometries doing a query like:

SELECT ST_NPoints(the_geom) FROM your_table;

or opening the layer in program like QGIS and see if all the features are drawn.

Improve this answer
6
  • Aha! I think you might be on to something there. The "empty" geometries have >2000 points. How can I get around this?
    – Mr_Chimp
    Commented Feb 17, 2011 at 14:01
  • 2
    Why get around it? The geometry is there. The only thing you are missing is seeing a lot of numbers and letter in a row, that will make no sense. pgAdmin is just avoiding to show it because it will just take a lot of bandwith to send it from the db.
    – Nicklas Avén
    Commented Feb 17, 2011 at 14:09
  • I am trying to get data from PostGIS in GeoJSON form. It returns an empty string for the problem rows. Meaning that, while the geometry is technically there, it's not much use to me!
    – Mr_Chimp
    Commented Feb 17, 2011 at 15:06
  • Ok, strike that! It returns an empty string in PGAdmin but works fine otherwise...which makes sense! Thanks again!
    – Mr_Chimp
    Commented Feb 17, 2011 at 15:27
  • No problem, I've found myself in this situation too. That's why I always prefer to work on the command line with psql.
    – amercader
    Commented Feb 17, 2011 at 15:33
2

Are you sure geometries are valids? You probably should test them before the import.

If the error persists, try to test them with SQL.

ALTER TABLE mytable ADD CONSTRAINT geometry_valid_check CHECK (ST_IsValid(the_geom));

You can read more about that in paragraph 4.3.5. Ensuring OpenGIS compliancy of geometries on http://postgis.refractions.net/docs/ch04.html

edit : Qgis command for validity check : Vector > Geometry Tools > Check geometry validity

Improve this answer
4
  • Ah, good call. I'm checking it now. It's a 115MB file so it's going to take some time.
    – Mr_Chimp
    Commented Feb 17, 2011 at 12:08
  • 6% done...and no errors so far. This is going to take ages. The data was originally a MapInfo .tab. I converted it to .shp in order to import it. I have converted it back to .tab without any problems and all shapes display correctly in MapInfo and QGIS so it would seem they are valid...
    – Mr_Chimp
    Commented Feb 17, 2011 at 13:43
  • It would seem only! GIS desktop softwares are able to display geometries even if they are invalids. If you don't have time to work on the full dataset, get a sample containing a least few shapes that ended-up with no geometry in postgis and check for their validity
    – simo
    Commented Feb 17, 2011 at 13:49
  • Yeah I see what you're saying but I think amercader has nailed it. Thanks all the same!
    – Mr_Chimp
    Commented Feb 17, 2011 at 14:07

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.