Tag Info

Hot answers tagged

7

You can do it via the geography type, using a geography index, or via the geometry type with some math to adjust for distortions in mercator. With geography: CREATE INDEX gb1900_geog_idx ON gb1900 USING GIST (geography(the_geom)); CREATE TABLE newtable AS WITH c AS ( SELECT a.cartodb_id, count(*) FROM gb1900 a, gb1900 b WHERE ...


4

From http://www.postgis.org/docs/ST_Buffer.html: Geography: For geography this is really a thin wrapper around the geometry implementation. It first determines the best SRID that fits the bounding box of the geography object (favoring UTM, Lambert Azimuthal Equal Area (LAEA) north/south pole, and falling back on mercator in worst case scenario) ...


4

In Postgres column and table names are not case sensitive and everything is converted to lower case before executing the query, see the docs. Therefore, SELECT SomeColumn FROM SomeTable becomes SELECT somecolumn FROM sometable unless you write it as SELECT "SomeColumn" FROM "SomeTable" In my view this is messy, especially when dealing with SQL ...


3

Use ST_Snap http://postgis.net/docs/ST_Snap.html as the final step. As input geometries you need your original polygon and extra vertices to be added as MultiPoint. The third term of the function is snapping tolerance. SELECT ST_Snap( ST_GeomFromText('POLYGON (( 7 7, 7 11, 11 11, 11 7, 7 7 ))'), ST_GeomFromtext('MULTIPOINT (( 11 9 ), ( 7 9 ), ( 9 11 ), ( ...


3

ST_SetSRID part of your query is breaking it. Using set SRID sets the SRID to 32650 without transforming it. So you are setting the SRID to 32650, and then trying to convert it to 32650, but it is already 32650 because you set it as so. So try: select ST_Transform(geom_line , 32650) from my_Table; If this does not work your data may not have a SRID to ...


2

You need to reproject your polygon to your raster projection, then you can clip the raster. This is how you can perform it : R = raster u_R = union of raster P = Polygon 1-Select tiles which intersects with your polygon Create table tiles_R as SELECT * from R where ST_Intersects(u_R.raster,st_transform(P.geom,st_srid(tiles_R.raster))) 2-Perform the ...


2

ST_Intersection(geomA, geomB) returns a geometry, then calculate areas with ST_Area.


1

Your expression works on one line compared to one polygon only: The black line touches Polygon 1 and doesn't intersect Polygon 1. In more detail: SELECT x FROM line, polygons WHERE ... means: Build a case from every pair of a line and a polygon, so number of cases is n_lines x n_polygons. Do the WHERE clause for every case and keep cases where it is true. ...


1

UPDATE brb_point SET geography = way::geography;


1

More specific to the answer by Redoute is that the two points are projected to EPSG:32619 (WGS 84 / UTM zone 19N), determined by the utility _ST_BestSRID(geog), which have transformed coordinates: ex1: SRID=-32619;POINT(414639.538157217 4428236.06463343) ex2: SRID=-32619;POINT(329274.505728464 4429672.97311587) These points are buffered by 10.0 m (in ...


1

You're not interpolating the Ruby variables into the string you pass to Postgres. In other words, the WHERE query doesn't know what "@poly" means, because it doesn't have access to your running Ruby environment. Either: Interpolate the variables into the string, e.g. #{@poly}, or Use placeholders How exactly you do this will depend on the syntax of the ...


1

Hmm it should be able to find it if postgis is in your database search path. Trying doing this: ALTER DATABASE your_db SET search_path=public,postgis; Then connect to your database again and do: CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION postgis_tiger_geocoder;


1

To explain what happened in the comments above: pnt__distance_lte according to the manual translates pnt__distance_lte=(pnt, 10) into ST_Distance(poly, pnt) <= 10 ST_Distance in postgis behaves differently for geography and geometry: For geometry type Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two ...


1

You can use the function ST_DumpAsPolygons: SELECT (ST_DumpAsPolygons(rast)).* FROM road_linestring_table,dem WHERE ST_Intersects(rast, geom); Afterwards you can rasterize the result.



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