New answers tagged

0

The problem is maybe with the type of your geometry as you see its type is Polygon Z in ArcGIS but you are trying to convert it as polygon to use it in PostGIS. I did some search and found this workaround: run Feature Class to Feature Class tool then (before executing it) in 'Environments' at the bottom > expand Z values > set 'Output has Z Values' to ...


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/


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....


0

ST_Split will split a line by a multi polygon and return a geometry collection by which you can retreive the lat,long of the start and endpoints. This statement: uses some similiar test geometry of 1 multiline and 3 polygon circles: combines the 3 circles into a single geometry with Union All and ST_Union gets the intersected geometry between the line and ...


0

Try this: $sql = "insert into table (geom, name, type) values (st_setsrid( st_makepoint($longitude, $latitude), 4326))"; In double quotes variable substitution is made by PHP, || is the SQL string concatenation operator.


0

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 ...


0

change line 92: DROP AGGREGATE aggregatepolygons(geometry, double precision, boolean); to this: DROP AGGREGATE IF EXISTS aggregatepolygons(geometry, double precision, boolean);


0

I think the problem is that your st_intersects mixes a geography and a geometry; I guess that Postgres is trying to cast your geometry to geography, and that fails because srid 2877 is, as your error messages says, not a lat/lon coordinate system. Easiest way is to do everything using geographies and st_geogfromtext (the st_buffer will also draw the circle ...


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));


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; ...


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 ...


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 ...


0

What I ended up doing to flip the columns to rows is the following: SELECT p.a_id AS a_id, unnest(array[p.sp, p.mp, p.ep]) AS a_pts, 0 AS b_id INTO TEMPORARY TABLE a_points FROM (SELECT DISTINCT ON (sel_a.a_id) sel_a.a_id AS a_id, ST_Line_Interpolate_Point(sel_a.geom, 0.0) AS sp, ST_Line_Interpolate_Point(sel_a....


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 ...


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....


0

Richard, the command st_startpoint returns the first point of a LINESTRING geometry as a POINT or NULL if the input parameter is not a LINESTRING.


0

This statement will generate a script to load all states: SELECT loader_generate_script(array(select stusps from tiger.state), 'windows'); I do it a batch at a time (states that begin with A) with this statement so I can make sure it's working properly: SELECT loader_generate_script(array(select stusps from tiger.state where stusps like 'A%' order by ...


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, ...


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....


0

You missed the geom column in your SQL query! So, if I rewrite the sql string to: sql = "(select id, geom from mytable)" it works for me...


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);


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 ...


0

Found it: osmosis --read-pbf south-america-latest.osm.pbf --tag-filter accept-nodes seamark:type=* --write-xml seamarks.osm


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

I've had some success with -append (to avoid recreating the table) and --config OGR_TRUNCATE YES (to truncate/empty the table before uploading new data) ogr2ogr -append -f PostgreSQL PG:"dbname=regional host=localhost user=*** password=***" Hsp_09_13.MIF -t_srs EPSG:3857 -nlt MULTIPOLYGON -nln postcode_sector_boundaries --config OGR_TRUNCATE YES (not ...


1

Not tested, but it looks to me that your csql should be something like: csql='SELECT field1,field2,etc FROM "Trainingset" t, "Polygon" p WHERE ST_Intersects(t.geom, p.geom)'


1

You can run any SQL query from the SQL tray on any layer or map visualization. In your case I'd do: Create a new column of type date on your table Run somethingl ike UPDATE mytable set mydatecolumn = to_date(stringcolumn,'MDDY') Remove the string column when you are sure your date column is correct Alternatively you can use the SQL API from any client (...



Top 50 recent answers are included