How to overlay layers with different SRID using QGIS and PostGIS? [closed]

Question

I have a map of the Netherlands imported from a SHP-file into my PostGIS environment with SRID 4289 (Rijksdriehoek Amersfoort). When I connect using QGIS to the PostGIS database and add the layer with municipality information, all goes well en looks great.

Also, I have a database table with a column with latitude and longitude information (I guess this is standard WGS84). This information I convert to a point in PostGIS SQL using: ST_SETSRID(ST_MakePoint(longitude, latitude), 4289).

But when I load a query on this table into the QGIS environment it plots the points at the wrong location (far below the municipality object and if I zoom in very much, I do see the shape of the Netherlands appearing). I have tried switching between 4289 and 4326 but no differences occur.

• Is this a conversion issue between the different SRID's or is there something else?
• What I can do to properly overlay the map with the points properly?

Answer 1

This is one of the most infamous issues in the GIS world: setting vs. transforming a CRS.

Take your time and get familiar with coordinate references, projections and transformations - it will make your life easier.

---

To the issue at hand; two hands-off options are

• simply load both layers in their native CRS into QGIS, with the project CRS set to your desired CRS; QGIS will do the projection on-the-fly, i.e.

  ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
  

• use

  ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), 4289)
  

  to actually transform your coordinate values into the desired CRS

---

Note that much of the power of PostGIS, and subsequently QGIS when working with it, derives off of using spatial indexes. And after all PostGIS primarily introduces specific data types to support referenced geometries, as well as a rich set of functions to work with.

It is good practice to have a proper GEOMETRY/GEOGRAPHY column holding your spatial data, and an index in place to speed up queries (significantly!). While it is possible to not have a dedicated column but with an index in place (functional index), there are few use cases only where that might be favorable.

Answer 2

For the WGS84 you should use EPSG:4326.

So:

ST_SETSRID(ST_MakePoint(longitude, latitude), 4326)

Answer 3

To expand on geozelot's answer:

1. Before I even turn on my computer, I decide which Coordinate Reference System (CRS, aka "projection") my project will use. In your situation, that would appear to be SRID 4289*.

2. Inspect each layer's properties to determine which CRS its internal data is stored in. With either QGIS or Arc Desktop, open the layer for inspection by double-clicking the layer from the software's table of contents.

3. If a layer is not using the project CRS established in step 1, re-project the layer to the project CRS. With QGIS use the Reproject Layer tool. With Arc Desktop, use the Project tool. For the love of all that is just and good in this world, DO NOT rely on setting the software's CRS properties. You MUST re-project the data itself!

4. Open a new, blank project in your GIS software.

5. Load any of your layers from step 3. The first layer loaded will "inform" the software which CRS you intend to use.

6. With all of your data in the same CRS, errors such as you reported will no longer appear. Peace and tranquility will reign upon your project!

*I don't have any experience with this CRS, but upon review, its units of measurement appear to be in degrees. There's nothing wrong with using degrees for display only, but if you intend to conduct any analysis or make measurements, such as distance, length, or area, the results will be non-sensical. Instead, establish a CRS for your project that uses meters or feet as the unit of measurement, and then re-project all the data to that CRS, as described above. Examples of a meter-based CRS for the Netherlands include either UTM 31N (SRID 25831) or 32N (SRID 25832).