Tag Info

Hot answers tagged

14

My guess is that you coordinate transformations have introduced tiny rounding errors (see an example below). As there is no way to set the tolerance in ST_Equals, this is causing ST_Equals to return false for some geometries that only differ in the nth decimal place, as the geometries have to be identical in every respect -- see the intersection matrix ...


4

If you have global data, use a geography type: CREATE TABLE points ( gid serial primary key, name character varying, point geography(Point,4326) ); CREATE INDEX points_point_idx ON points USING gist (point); Then use a function that can use the spatial index, using a metric distance (see ST_DWithin) SELECT name, point FROM points, (SELECT ...


3

You can go with the SQLExecuter, the initiator would need to be a list of database values [keys] you want to delete. Then using something similar the SQL statement below to execute the delete query should work. DELETE FROM table WHERE key = '@Value(key)'; Or you can use the DatabaseDeleter


3

ST_Polygonize will do the job: CREATE VIEW boundarypolygons AS SELECT g.path[1] as gid, g.geom::geometry(polygon, 31492) as geom FROM (SELECT (ST_Dump(ST_Polygonize(geom))).* FROM boundary ) as g;


3

ST_Collect is probably not the function you are looking for, as this simply combines geometries into a geometry collection of some type, and does not actually union/dissolve them. ST_Union, on the other hand, does dissolve overlaps, and assuming polygonal input (which is most probable in conjunction with ST_Buffer and an input table called point), and ...


3

select * from eating_drinking where ST_GeometryType(way)='ST_Point'


2

Did you run ST_IsValid check on your geometries? If they are invalid, all bets are off. ST_Intersects and the other family of GEOS spatial relationship functions will often just return false because the area is not well-defined from an intersection matrix point of view. The reason doing ST_Buffer probably works is because it's converting your invalid ...


2

Are you in the USA? If so, determine the State Plane coordinate system that is used by your area, and what the SRID is, for example, What is my State Plane zone in Colorado? I suggest using State Plane as the units are FEET, which can easily be converted to miles. I use SRID/EPSG:2877 So now use an ST_Transform statement which will project your data ...


2

Assuming you have at least PostgreSQL version 9.3, you can use a few JSON functions and operators to extract the relevant parts of the GeoJSON specification required by ST_GeomFromGeoJSON to create geometries. Try the following, where you can replace the JSON in the top part: WITH data AS (SELECT '{ "type": "FeatureCollection", "features": [ { ...


2

I think that you have to configure postGIS library as extension in your database. Here an example.


2

This is a case sensitivity/quoting issue. "PointsForGpxExport" and PointsForGpxExport are not the same table name. PostgreSQL, per the SQL standard, case-folds unquoted identifiers. (It case-folds to lowercase, where the standard says uppercase, though). So when you write PointsForGpxExport, PostgreSQL treats that as the same as pointsforgpxexport. Since ...


2

Might want to try HausdorffDistance. Suited for this kind of question. SELECT ST_HausdorffDistance('0101000020110F0000FE546AD2413E5EC16D56617C93C74FC1'::geometry, '0101000020110F00009D7E6384413E5EC10185DD7F93C74FC1'::geometry); Detailed here: http://postgis.net/docs/ST_HausdorffDistance.html And as explained at /wiki/Hausdorff_distance: Informally, ...


2

What you are looking for is essentially an upsert (albeit without the update part). This is not part of ogr2ogr and is fairly complicated to implement in Postgres, see the docs, for more information than you would ever want to know about upserts. A simple alternative, would be to use a temp table to insert into from ogr2ogr and then run an insert for only ...


1

Since you are using PostGIS and QGIS, you can try to convert them using both, to decide which one is best suited for your problem. To use PostGIS, the LINESTRING must be closed. You can check if they are closed with the query: select gid, st_isclosed(geom) from boundary; If the lines are closed, you can create another table to check the results, with: ...


1

The Soil Data Viewer from the NRCS makes querying single attributes from SSURGO data relatively easy.


1

You could add row numbers to the points and then join points and non-spatial table based on row number and ogc_fid (assuming ogc_fid starts with 1).


1

is the area field a float? you may want to try creating the area index (clustered or non) on an integer field (if your use case can accept the generalization).


1

There are a few issues to consider. First, have you considered using a VIEW? I.e. CREATE OR REPLACE VIEW places_with_coods AS SELECT gid, ST_Y(geo::geometry) AS lat, ST_X(geo::geometry) AS lon FROM places; However, if you want to use triggers, these changes will make things work. The important pieces being that NEW is the row, which can be modified ...


1

You could do the following: Find the nearest edge to A and B Then use ST_LineLocatePoint und you get a ratio of where the nearest point lies on that edge. With ST_LineInterpolatePoint you get the coordinates of each point on their nearest edge and with ST_LineSubstring you could also get the partial geometries, but that's not necessary for the shortest ...


1

Vector solution: Intersect or Union the two layers. Both functions should be available in all three of the softwares you have tagged. Note that Intersect returns only the areas of overlap, while Union returns both areas of overlap and areas that are one layer and not the other. Hence Union will allow your results to total 100% of inputs, while Intersect may ...


1

Currentyly there is a method to filter geometries within some distance in PostGIS that uses spatial index: http://postgis.net/docs/ST_DWithin.html



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