Hot answers tagged

4

One possible solution is to use St_ClusterWithin. This creates geometry collections of all the geometries that are within a distince, d, of each other, and takes the form ST_ClusterWithin(geom, d). Start by using ST_ClusterWithin. Wrap this function in unnest and ST_CollectionExtract which takes a GeometryCollection and a number, in this case 1, points, ...


4

You could collect all distinct values for your groups and aggregate them in the db and create a new table. You will get a new field with aggregated strings. And you actually use those for the labeling. CREATE TABLE AS... SELECT string_agg(field_with_distinct_values, '?') FROM table GROUP BY field_for_the_relate You then join this table in Qgis and use in ...


3

I've reproduced your problem and did some benchmarking. I've created an intersection between a polygon layer and with horizontal lines (every km), then with lines at 45°, and then with the same lines at 45° split every km vertically (which equals to sqrt(2) = 1,414 km). Here are the results of the intersection query: horizontal : 8.7 sec (no index) ...


3

It means that those addresses are located at more than 50 units from a street. They are therefore filtered out by your (inner) JOIN. You can increase the distance in ST_Dwithin or use a LEFT JOIN while inverting st and ad in your query. But your query does not return the nearest street, it returns all street/address combinations which are less than 50 ...


3

Sounds like you are looking for ST_DWithin(): SELECT * FROM points as r LEFT JOIN points as s ON ST_DWithin(s.geom, r.geom, 2); and this to get the average: SELECT r.id, s.id, AVG(ST_Distance(r.geom, s.geom)) FROM points as r LEFT JOIN points as s ON ST_DWithin(s.geom, r.geom, 2) where r.id <> s.id GROUP BY r.id,s.id; ...


3

You could try the following which is a variation of the example from the documentation SELECT DISTINCT s.id, s.clazz FROM berlin_2po_4pgr s INNER JOIN berlin_2po_4pgr m ON ST_DWithin(s.geom_way, m.geom_way, 2000) WHERE m.clazz = '11' and s.clazz != '11';


2

Your subquery is wrong and is probably evaluating to null so that no street segments are close to the null geometry. running the subquery alone would help you identify the problem: SELECT geom_way WHERE clazz = '11' Including the FROM clause to your subquery should return some results: SELECT id, clazz FROM berlin_2po_4pgr WHERE NOT clazz = '11' AND ...


2

You're missing a GROUP BY to specify aggregation of the rows. WITH clusters AS( SELECT row_number() OVER () AS cluster_id, unnest(ST_ClusterWithin(table.geom, 250)) AS gc FROM table) , unclustered AS( SELECT cluster_id, unnest(gc) AS geom FROM clusters) SELECT cluster_id as gid, string_agg(table.amenity, ';' ORDER BY table....


1

You might consiider PostGIS FDW (Foreign Data Wrapper) - it will connect to remote files such as Shapefiles or CSV, and they will appear and be queryable just like 'normal' PostGres tables. I think you'd need to use the GDAL/OGR FDW library - it supports shapefile connections. But don't take my word for it: http://postgis.net/2014/12/19/postgis_fdw/


1

You can use ST_ClosestPoint to return the projection of your point on the linestring. SELECT --poi.name, --poi.city, ST_AsText(poi.the_geog) AS poi, ST_AsText(ST_ClosestPoint(road.the_geog::geometry, poi.the_geog::geometry)::geography) AS closest_point, ST_Distance(road.the_geog, poi.the_geog)/1000.0 AS distance_km, ST_AsText(road.the_geog) AS ...


1

Try to use ST_GeogFromText instead of ST_GeomFromText select * from circles where st_intersects (towers.the_geog,ST_Buffer(ST_Transform(ST_GeogFromText('POINT(-105.04428 39.74779)', 4326), 2877), 1500));


1

You can use UNION SELECT to combine various columns into rows, so from the output of step 2 to stack sp, mp, ep into the same column: SELECT a_id, sp FROM points UNION SELECT a_id, mp FROM points UNION SELECT a_id, ep FROM points You can then use the <-> operator, for finding the closest point in one table to another, used in the ORDER BY ...


1

I would say the geometry you're creating should be in SRID 4326 (the point from Google), And you should transform the geometry in 2263 to 4326 to match (or really the other way around), so your query might look like this: SELECT t1.address FROM t1 WHERE ST_Contains( ST_Transform(ST_SetSRID(geometry, 2263), 4326) , ST_GeometryFromText('POINT(-74....


1

Not sure what you mean by installed GDAL, PROJ.4, and GEOS via OSGeo4W. PostGIS already comes packaged with those and (ones in stackbuilder and download.osgeo.org page is not compatible with OSGEO4W packaged ones). So if you are copying the ones you got in OSGeo4W into your PostgreSQL folder, it won't work. Also make sure you use 64-bit postgis with 64-bit ...


1

You are missing ST_AsText, as @WKT mentions above. This should work for you: select ST_AsText(geom) from polygon where ST_Intersects(ST_GeographyFromText ('POLYGON((210000 2400000, 300000 2300000, 330000 3708400, 210000 2400000))'), polygon.geom);


1

You have to set up the QgsDataSourceURI like in this post: uri = QgsDataSourceURI() uir.setConnection('host', 'port', 'database', 'username', 'password') sql = 'SELECT * FROM myschema.mytable JOIN othertable USING(id)' uri.setDataSource('', '(' + sql + ')', 'geom', '', 'id') vlayer = QgsVectorLayer(uri.uri(), 'LayerName','postgres') QgsMapLayerRegistry....


1

You need to use ST_GeomFromText to specify your point geometry, note that the coordinates are separated by a blank space and not a comma. Try the following (changed one of your point coordinates so that it isn't the same as the other): INSERT INTO myTable(id, name, geom) VALUES('1','test', ST_SetSRID(ST_MakeLine(ST_GeomFromText('POINT(528115 181037)'),...


1

st_split(line,point) will produce a collection of geometries which you can then get each st_length. st_Dwithin could be used to locate polygons and then st_distance could be used between the polygons to get the street width. How about this statement which uses st_distance, st_endpoint and st_startpoint to return: the id of the street (I added this so I ...



Only top voted, non community-wiki answers of a minimum length are eligible