6

I loaded a few Shapefiles from different sources into a PostgreSQL database, when I visualize them in QQGIS some of them are not shown.

This how i added the geometry column in my tables:

SELECT AddGeometryColumn ('public','table_geom','geom',4326,'MULTIPOLYGON',2, true);

And this is the SQL script that I used to change the srid of the Shapefiles I uploaded to the table:

SELECT UpdateGeometrySRID('public', 'shapefile_table','geom',4326);

I added the geometry column from the table created from the Shapefile, there it wasn't any trouble with this.

This is how i defined my table:

  CREATE TABLE table_geom
    (
      id serial NOT NULL,
      geom geometry(MultiPolygon,4326),
      CONSTRAINT pk_table_geom PRIMARY KEY (id_ageb),
      CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
      CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),
      CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE table_geom
      OWNER TO postgres;

The thing is, i can visualize correctly the source Shapefile in QGIS but not from the database. What am I missing? I already checked the srid is the same in all tables but that seems that is not the problem. Any hints?

Quick edit: i think is a bug(?), probably because the table data is too big, now some of the layers are being shown from one table, but not all.

Edit 1: I see now what is the problem, the geometry from the table is being drawn but not correctly,as i mentioned before if i load the source shapefile in qgis the area is shown where it should be, but if i load from the database this area is drawn elsewhere, but why? i changed the srid in all loaded shapefiles

  • It can be a projection problem. Are the source shape files in WGS 84? Did you check in PostGIS (e.g. pgadmin3) your data are in the database (e.g. select count(*) from table_geom or select ST_Area(geom) from table_geom)? – Zoltan Jan 7 '16 at 7:56
  • @Zoltan the sql select count(*) from table_geom gives as result count bigint 78,889 and select ST_Area(geom) from table_geom gives as result st_area double precision and many rows with numbers, and yes i just checked the files they are in WGS 84, something strange just happen i have loaded 2 shapefile into the database in the same table, one contains the rural areas and the other one the urban areas, when i visualize the table with Qgis the map should appear complete, but sometimes it just load the rural areas and others the urban areas but not both, i have no clue why. – B.J. A.A. Jan 7 '16 at 16:50
  • Can you test the PostGIS connection using smaller data sets? Have you created spatial index in PostGIS? I can only guess... – Zoltan Jan 7 '16 at 18:37
3

Notice that UpdateGeometrySRID just "sets" a new SRID to the geometries, it does not change the geometry coordinates values. Have a look in documentation about it.

You will probably need to transform your data either before you import it, during the import, or in PostGIS already.

For the last option (that I assuming that is your case), you need to set the correct SRID (the original one from your shapefiles) and then transform your geometries. You can do it in one single step.

For example, if your original data were in 3857, you can do the following:

ALTER TABLE table_geom 
ALTER COLUMN geom TYPE geometry(MULTIPOLYGON, 4326) USING ST_Transform(ST_SetSRID(geom,3857),4326);
  • How i can check the original SRID from the shapefile? – B.J. A.A. Jan 7 '16 at 22:09
  • 1
    Well, or you have a prj file in your shapefiles with that information or you need to ask to whoever gave you the data. – Alexandre Neto Jan 7 '16 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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