Continuing on clustering queries

[karsten]

Hi Max,

Possibly the QGIS user group is best but here are my 2 cents:

QGIS can both use views and direct SQl query results (e.g. via DB manager)
and display then as layers on the map.
There a is couple of things you should make sure so that this will work for
you:

- make sure the geometry as a set SRI so QGIS know how to display it wit any
other data on the map
- In DB manager e.g. when you run a query make sure you have a unique ID and
the correct geometry column / return value selected
If you do not have a unique id or are unsure use the row_number to cerate
your own on the fly
E.g. select *, ROW_NUMBER() OVER() as gid from thetable
-- make sure whatever functions you use with st_collect end up in a geometry
type that is know to QGIS , check e.g. making the sql query into a db view
an and in QGIS browser inspect of the geometry does not show a ? Sign. In
this case you might need to force it to a specific type using some
additional PostGIS functions

Cheers
Karsten


-----------------

I've adapted some of your recommendations. One of the targets of this effort
is to add a layer to an existing QGIS project: various energy infrastructure
of the U.S./North America.

So, I've taken a portion of your recommendations, and have built the
following SQL:
DROP TABLE shapefiles.filling_station_clusters;

CREATE TABLE shapefiles.filling_station_clusters AS SELECT city, state,
ST_ClusterDBScan( ST_Transform(geom,2163), eps := 0.3048*5000, minpoints
:=1) OVER () AS cluster_id, aa.geom FROM shapefiles.atp_filling_stations aa
WHERE aa.amenity = 'fuel'
AND aa.ctry_code = 'US' AND aa.geom IS NOT NULL;

The reason is that it doesn't seem that QGIS can work w TEMP tables.

>From this, I want to create a layer; I use the following SQL:

SELECT cluster_id AS cid1 ,
ST_Collect(ST_MinimumBoundingCircle(ST_Collect(geom)), ST_Collect(geom)) AS
geom, COUNT(*) as ct FROM shapefiles.filling_station_clusters GROUP BY 1;

(for some reason, it rejected the SQL if a result was returned with column
name of cluster_id - possible reserved name?)

This SQL variant does not return errors, but it isn't visible in my QGIS
project (it's an ongoing project with about twenty layers built with
PostGIS).

Any advice?

Should I direct this inquiry to the QGIS list?

Thank you,

Max