0

I created two tables with PostGIS, within which I have inserted a shapefile of an Italian region (Apulia) in the first, and the coordinates in the second table.

In both tables are set with the geometry srid 4326, but by importing layers into QGIS, layers are not properly overlapped.

The procedure I followed is the following : I downloaded the following shapefile (link), and I created the sql script with command

$ shp2pgsql -a -c -D -s 4326 -I R16_01 public.puglia > puglia.sql

within the script, the geometry is added to the table in this way:

ALTER TABLE "public"."puglia" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('public','puglia','geom','4326','MULTIPOLYGON',2);

similarly, for the table coordinates

SELECT AddGeometryColumn('public','coord','geom','4326','POINT',2);

then I insert into the table coord some coordinates, also occurred via google maps ...

INSERT INTO mapp (gid, cod_istat, geom)
VALUES (2,3, ST_SetSRID (ST_MakePoint (41.8976987992015, 16.121389858102294), 4326));

When I import the layers in QGIS , the overlap is not what I expect . The coordinates that I post are far away from where they should be.
  what's wrong ?

  • How does your polygon layer looks like? Is it well projected or is it distorted in QGIS? Your source shape file has the EPSG:23032. Do you use ogrinfo -al -so to check your metadata? – Stefan Dec 9 '13 at 11:31
  • Hi @StefanB. , my shapefile is displayed perfectly, with no distortion. I do not know ogrinfo, but the output is this: 'PROJCS["ED_1950_UTM_Zone_32N"' quindi io dovrei convertire lo shapefile in 4326? how? – Pierock Dec 9 '13 at 11:39
  • I'm currently testing your data. What is your project CRS in QGIS? The coordinate field (bottom right) shows coordinates with 7 aligned numbers running the mouse over the polygons!? Your inserted point data probably has the right UTM coordinates (xx.xxxx...). So there is the gap. The problem should be the projection of the polygon layer. – Stefan Dec 9 '13 at 11:59
  • You can transform your layer CRS with ogr2ogr -f "ESRI Shapefile" output.shp input.shp -s_srs EPSG:23032 -t_srs EPSG:4326 – Stefan Dec 9 '13 at 12:18
  • @StefanB. I tried to convert the shapefile with the command ogr2ogr -f "ESRI Shapefile" R16_012.shp R16_01.shp -s_srs EPSG:23032 -t_srs EPSG:4326 and then to insert it into PostGIS, but the result is still wrong in QGIS. The projection in QGIS is 4326 – Pierock Dec 9 '13 at 13:06
0

At first: I don't know if this solves the main problem, but you interchanged the longitude and latitude values in the ST_MakePoint clause.

Like the answer from Andre Joost.

  1. Import the shapefile with the -s 4326 and with the transformation with ogr2ogr (see comments). Create the point data table with the same EPSG (4326)
  2. Find out CRS from shapefile metadata (see ogrinfo in one of the comments). Define project CRS with the EPSG:23032 and enable 'on the fly' CRS transformation
  3. Load both, the point and the polygon layer into canvas and do not change the CRS of the layers.

After that the layer should overlay correctly. The CRS units are in the given project CRS and not in degress.

  • Yes @Stefan B. you, you're right. I corrected this error, but as I said, now the "zone" is correct, but the distance between the points and the rotation of the square that form are incorrect. they are out of scale with respect to the shapefile. I don't know why... – Pierock Dec 9 '13 at 13:45
  • I'm sorry @Stefan ... the output is correct! Unfortunately, the shapefile is not very accurate, but the overlap works well! problem solved! thank you very much! – Pierock Dec 9 '13 at 13:59

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.