Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Hot answers tagged

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