3

I used virtual layer query to create a buffer for a point as this allowed me to transform its SRID using the PostGIS function ST_Transform, which is not possible with the ready-to-use Buffer algorithm of QGIS. The query is:

SELECT st_buffer(st_transform(st.geometry,3003),75) as geometry,
       st.* 
FROM   st

Once the buffer was created as a virtual layer I exported it as table to my PostgreSQL environment to use it in a materialized view. I realized I could save one step by directly putting the buffer query into the materialized view, instead of first creating a virtual layer in the GIS and exporting it to PostgreSQL. What I noticed is that the same query used in a virtual layer and in PostgreSQL produced two buffers with different accuracy. The one from the virtual layer appears to be a nearly perfect circle, while the one from PostgreSQL (re-imported into QGIS with a materialized view) had a precision of 8 segments per quarter of circle, which seems to be the default when no precision is specified as it happens in the ready-to-use Buffer algorithm of QGIS. Why does the virtual layer produce a nearly perfect circle and how can I make the two match in accuracy?

1 Answer 1

6

When using a virtual layer, even over a postgres layer, you are not using postgis functions but rather spatialite functions.

We can see that st_buffer, in spatialite, has a default of 30 segments per 1/4 circle, while PostGIS only uses 8.

To have the same rendered polygon, you can specify the 30 segments in PG.

SELECT st_buffer(st_transform(st.geometry,3003),75,30) as geometry,
       st.* 
FROM   st
2
  • 1
    it worked! My knowledge of database (besides having fun building SQL queries) is quite limited. Is it correct to state that spatialite and postgis are two distinct, yet very similar, DB querying languages (or better extensions of languages) for two different environments (spatialite DB and PostgreSQL)? Jun 22, 2023 at 15:18
  • 2
    @MicheleCordini It depends what you consider by a "language". If we consider the implementation of functions, then yes, Spatialiate is a module for SQLite and PostGIS is a module for Postgres. But the surface language is SQL and the function names are (more or less) standard, so code written for one should work with the other (with some subtleties as you have discovered)
    – JGH
    Jun 22, 2023 at 17:38

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.