2

Given an existing geometry in QGIS 3.28+ (e.g. from a temporary scratch layer), is there a quick way (i.e. with the lowest amount of user actions) to generate the PostGIS geometry definition (raw SQL statement) that would fit into whatever SQL query (ideally using the current map SRID)?

E.g. the geometry that would fit into:

SELECT 'bar', 'baz', <the_generated_postgis_geometry_object_from_qgis>;

5
  • 1
    If inside the DB Manager just SELECT ST_AsEWKT |ST_AsEWKB(geometry) FROM <scratch_layer> [WHERE ...]; in the layers section, and copypaste the result into the PostGIS query (wrapped in ' and suffixed with ::GEOMETRY of course). Or just do your thing in SpatiaLite SQL.
    – geozelot
    Feb 16 at 21:03
  • Seems promising, but this is giving: Query preparation error on PRAGMA table_info(_tview): no such function: ST_AsEWKT in the DB manager when executing the query on the scratch layer. (QGIS 3.34.3) Feb 16 at 21:18
  • 1
    Well, best to check the SpatiaLite function reference; it has to be AsEWKT|AsEWKB (no ST_) in its dialect.
    – geozelot
    Feb 17 at 8:00
  • Indeed, but then the command prefix the query with the SRID, which I guess is not suitable for directly inserting into a PostGIS query: SRID=4326;POLYGON((1.434694... Feb 17 at 8:36
  • 1
    Oh on the contrary, EWKT is a PostGIS own extension to WKT; just cast that string to GEOMETRY and it get's the SRID set for you.
    – geozelot
    Feb 17 at 8:59

1 Answer 1

2

You can add a virtual field (type text) that gets the text representation of the geometry and ads the projection. Then you can copy/paste from the attribute table.

'st_setSRID(\'' || geom_to_wkt($geometry) || '\'::geometry,' ||   substr( @project_crs,6) || ')'

enter image description here

You can also adapt this into an action so you can right click a feature to compute and copy the geometry text.

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.