4
You can use DB Manager for this purpose but first you need to create postgis connection to that database using QGIS. To create PostGIS connection
Open QGIS and right click on PostGIS icon in QGIS browser as shown
in image below.
Provide the DB connection details in the form shown below and click
ok.
After you create connection open DB Manager
In ...
3
It is slow because there is no spatial index on the buffered geometries.
You could create a temporary table with a spatial index, populate it with the buffered geometries, and run the query on this temp table.
3
The query is taking a long time because it is doing a cross products between tables a and b (and poly).
So if poly has 1 polygon only, and both a and b have 1,000,000 features, you end up computing st_DWithin 1,000,000,000,000 times.
You can try to combine the two update using a join instead. You may have to use a subquery to achieve it though, as ...
3
To be noticed: Area and length measurements in EPSG:4326 and do not give reasonable results but that's dealt with many other quesions https://gis.stackexchange.com/search?q=epsg%3A4326+area+
Re-ordering the multipolygon members by area can be done by exploding the multipolygon with ST_Dump, ordering the primitive polygons by the result of ST_Area and ...
3
profile_type_id is not in your select statement so the outer select can't see it. You need to try something like:
CREATE TABLE cross_section_lines AS
SELECT st_makeline(geometrypoint) as cross_section,
cross_section.profile_code,
cross_section.administrative_area_id,
cross_section.profile_type_id
FROM (SELECT ...
2
you can use db manager for this.
Everything is explain in this post :
How to export shapefiles from QGIS to PostGIS?
2
I believe this query would be faster and produce the same / equivalent results:
WITH temp AS (
SELECT
r.road_id,
ST_Intersection(ST_Buffer(r.geom, 3), l.geom) AS newgeom
FROM lines l
JOIN roads r
ON ST_DWithin(l.geom, r.geom, 2.99) -- 2.99 instead of 3 to exclude geometries that only touch
)
SELECT
road_id,
ST_Union(newgeom) AS ...
2
Since you don't want the column in the GROUP BY section, you would have to aggregate the values of this column.
For instance, you can use STRING_AGG and possibly keep only the distinct values via the DISTINCT keyword.
You may have to cast the column to text as well
SELECT id, string_agg(distinct colA::text, ',')
FROM mytable
GROUP BY id;
1
Use this query to get the route for the sequence of given nodes.
select fd.seq as seq,fd.geo as geo from ( SELECT ROW_NUMBER() OVER (ORDER BY now() ASC)
AS seq,(SELECT (ST_Collect(foo.geo)) FROM (SELECT e.the_geom as geo FROM
pgr_dijkstra('SELECT gid as id,source, target, rcost_len AS cost FROM edges',orig,dest,
false, false) AS r, edges AS e WHERE r.id2 ...
1
UserB connects to the DB (read only), then load the QGIS project. Its connection stops being used at that point.
Within the project, the credentials of userA are saved and used for the layer, by anybody that can access the project.
You would have to re-save the project in the DB without the credentials.
1
1) reordering the wrong geometry type multipolygon can not be!
2) reorder them...
SELECT id, area, ST_CollectionExtract((ST_Multi(ST_Collect(geom))),3) geom
FROM source_table
GROUP BY id, area, geom
ORDER BY id, area, geom DESC;
P.S.
So let's summarize.
Together, through my presentation we get the following picture.
3) Arguing further, using this set ...
1
You want ST_ENVELOPE as in
SELECT 'place1', st_asText(ST_Envelope(ST_GeomFromText('POLYGON((-71.1776585052917
42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081
42.3903033653531,-71.1776585052917 42.3902909739571))',4326)));
Gives
POLYGON((-71.1776820268866 42.3902909739571,-71.1776820268866 42....
1
Very good answer from @dbaston.
However, returning an empty geometry instead of null might cause issues because returned empty geometry has no srid.
St_Intersection might return MultiPolygon as well.
This updated function was really useful for me:
CREATE OR REPLACE FUNCTION PolygonalIntersection(a geometry, b geometry)
RETURNS geometry AS $$
SELECT ...
Only top voted, non community-wiki answers of a minimum length are eligible
