Tag Info

New answers tagged

0

In PostGIS you can use ST_Buffer to add a little margin to one of the polygon layers, then use ST_Intersection to get the curbs polygon. Now the ST_StraightSkeleton may give you what you want (haven't actually tested but it should work).


1

Something like this? -- s1: the point WITH s1 AS (SELECT ST_SetSRID(ST_MakePoint(9.116447, 60.548203), 4326) AS geom), -- s2: polygon(s) where the point is in s2 AS (SELECT p.geom FROM s1, polygons_wgs84 p WHERE ST_Intersects(p.geom, s1.geom)), -- s3: polygon(s) which touch s2 s3 AS (SELECT p.geom FROM s2, polygons_wgs84 p WHERE ...


0

Try something like this: SELECT * FROM polygons_wgs84, (SELECT * FROM polygons_wgs84 where ST_Intersects(geom, ST_Transform(ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(9.116447, 60.548203), 4326), 3857), 1000), 4326))) sub WHERE ST_Touches(sub.geom, polygons_wgs84.geom)


1

Bounding box is rectangular and therefore ST_DWithin gives wrong result by using a circle for making the selection. Your workflow could be: Define a point Buffer the point and create a circle Make minimum bounding box around the circle Use this newly build box for selecting the features Thus, if we forget the trouble with geometric coordinates, the SQL ...


1

My problem was fixed by removing the format parameter, so the SQL query itself actually wasn't at fault here (username.cartodb.com/api/v2/sql?format=GeoJSON&q=SELECT...).


0

SELECT pt.id FROM points_table pt WHERE ST_DWithin(ST_GeomFromText('POINT 0 0', 3857), pt.geom, 500); This might be one way to solve your problem.


1

It's possible that the geometries are too large in terms of vertices, which will result in a very long WKB value that would need to be returned by the SQL API. If you don't need a high precision for them you could use ST_Simplify_PreserveTopology in order to reduce a little bit the length of the geometries.


2

You need to make sure your set search_path call always includes the schema postgis is installed in. So instead of doing: SET search_path=vmplan201506; Make sure you are doing SET search_path=vmplan201506,public,tiger; If you have other tables in public you don't want seen, then move postgis into another schema and make sure that schema is always ...


0

The answer is what @Redoute wrote but change ST_Intersects to ST_Crosses. Because the blue line also intersects with polygon 2 when it touches him...


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


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


0

The code in my above question does work for some queries, but is unreliable. I have ended up settling with for now this query which takes in elmo's process but without creating any new tables. I am not sold on it as it requires more work than I would like - an extra half a second to return. SELECT ST_AsGDALRaster(rast,'GTiff') FROM ( SELECT ...


1

UPDATE brb_point SET geography = way::geography;


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


0

The ST_Simplify uses the Ramer–Douglas–Peucker algorithm which takes a user-defined tolerance, then iterates over the geometry object and removes points that fall within the algorithm's criteria + the tolerance. The tolerance is based on your SRID, so if your geometry is in 4326 then the tolerance is in decimal degrees. However, the algorithm doesn't change ...


3

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


0

Since you are searching for identical points only, the bounding boxes intersect operator "&&" will be most efficient. In case your tables don't have spatial indizes, create them: CREATE INDEX ON r USING gist (geom); CREATE INDEX ON e USING gist (geo); Then for each linestring lookup the matching endpoints and the bigger km value: UPDATE r SET ...


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


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


0

To be explicit about nickves' answer: ogr2ogr -append -f "PostgreSQL" PG:"dbname=db" shapefile.shp -nln mytable


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.


0

Fresh on the QGIS plugin Repository: http://plugins.qgis.org/plugins/SelectWithin/ This will do a Centroid within query or a Point on Surface within query.


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

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


0

Don't know your data. A template could look like: select st_distance(mall.geom, roads.geom) as dist from roads, mall where roads.name = '16th cross' AND mall.name = 'xyz' order by dist limit 1


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


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


0

You can try the merge tool wish merge a lot of shp to one


1

Viewsheds have been mentioned. These will show which grid cells are visible from a given point. They don't tell you the distance, however. If it's the distance you're after, SAGA GIS has the ability to do this. The tool is under Terrain Analysis > Lighting > Visibility You can either specify one or more observer coordinates from a shape layer, or use ...


0

You can search for OpenJUMP it's a GIS Java software, you can use it to display PostGIS files or for developing Plugins. You can read more about this GIS Java software here.


1

I stumbled upon an old project that made use of the "layers" in the porject.yml file so I inserted my layers in the format noted below and was able to manage the draw order of features this way. Note: The top of the list is the lowest layer and the last list item is draw on top of the preceding items. layers: - landuse - waterway - water - ...


3

You can dump the border of polygon with ST_ExteriorRing, then it will find the closest point on the resulting linestring, if you want a point that lies just a little bit outside use ST_Buffer first to add a little bit to the original polygon. WITH polygons AS( SELECT ST_Buffer(ST_GeomFromText('POINT(0 0)', 4326),2) as geom-- this circle will pretend to ...


1

The ERROR: function addGeometrycolumn(unknown, unknown, unknown, unknown,unknown, integer) does not exist means that the postgis extension is not installed on your postgresql. Go here and install it.


1

If you want the intersection, that should be one of the things you SELECT. If you only want rows where there is an intersection, that goes in the WHERE. So you probably wanted (no test, because you didn't provide the full table schema) something like: SELECT id, time, ST_AsText(geom_houses), ST_AsText(ST_Intersection(geom_line, geom_houses)) FROM my_table ...


2

If you're not familiar with projections and the like, you might want to do the NOAA course on this: http://coast.noaa.gov/digitalcoast/training/datums (note that you do need Flash, and its narrated so you need sound; but you can skip the registration if you like, and the ArcGIS specific parts at the end are obviously not needed). Once you have an ...


1

It seems you can use ST_Node to do this faster than ST_Union. I used this query, which I think gives the same result: CREATE TABLE boundaries AS SELECT (ST_Dump(ST_Node(ST_Collect(ST_ExteriorRing(geom))))).geom AS geom FROM circles; What I'm doing is collecting the exterior rings and then noding them, which splits them into individual segments, ending at ...


0

for more info check: Enabling PostGIS PostGIS is an optional extension that must be enabled in each database you want to use it in before you can use it. Installing the software is just the first step. DO NOT INSTALL it in the database called postgres. Connect to your database with psql or PgAdmin. Run the following SQL: -- Enable PostGIS (includes ...


3

Several ways to do this. One is to find the distance and order ascending. SELECT geom FROM polys, points WHERE points.gid=1 AND ST_DWithin(poly.geom, points.geom, 1000) ORDER BY ST_Distance(a.geom, b.geom) LIMIT 1; I added the ST_DWithin call to show how you might limit the number of candidates (it will make use of the index.)


1

I use ST_ClosestPoint to return the closest point from each polygone, then I calculate the min distance SELECT foo.* from (SELECT min(st_distance(a.geom,ST_ClosestPoint(b.geom,a.geom))) from polyg a,point b) foo


1

You need to add postgis to your search path: Try ALTER DATABASE name_of_your_db SET search_path = public, postgis; Then disconnect from your database and reconnect. It should work.


1

You're getting duplicates because ST_Dump is breaking up multi geometries and/or geometry collections into constituent parts, each of which has the same ID of the original geometry. So if a MultiPolygon has 5 Polygons, you'll get 5 Polygons with the same ID. You can assign a new ID to each shape if you want, but keeping the original ID will help with joins ...


0

Building the union of thousands of circles will be the expensive part. Actually finding the points inside the constructed object will be moderately fast, since the prepared geometry routines will kick in and you'll have an internally indexed query polygon. But building that polygon, could take a while. WITH ( query to build and union circles ) AS circles ...


1

This line is the problem uri.setDataSource("",sql,"long","","lat") The error message you're getting says that the table "select long,lat from ..." doesn't exist. The reason is you're passing the sql as the 2nd parameter; this should be the table name instead. From the API documentation, the parameters should be in this order... ...


2

Based on the great idea of @radouxju's responce I resolve this issues. Instead of using Reclass function I used MapAlgebra function so the query is : SELECT ST_MapAlgebra(a.rast,b.rast, '(ceil([rast1]/10)*10)+5') AS newrast from my_raster1 a, my_raster2 b I used 'ceil' funtion to get the smallest integer not less than argument see this link And this is ...


0

The source and target parameters in pgr_dijkstra are id's of vertices, not edges. I assume 41 and 50 are edge id's = column gid in table network. Moreover pgrouting is constrained to 32bit-integers as edge- and vertex-id's, so you can't use OSM-id's directly, as they are too big.


4

if you have a constant interval, then you do not need a look up table. You can use the following formula (int(raster@1) / interval) * interval + interval/2 of course, / must be an integer division (rounding to the integer below)


4

You should be able to obtain logs from Postgresql once you configure it. I typically will set log_statement = 'all' in my postgresql.conf, even on production databases (with a separate cleanup routine to clear them out every 2 weeks). Also change the log_line_prefix to something that includes at least the process id, something like: log_line_prefix = '%t ...


4

All EPSG:4326 geometries calculations are done with degrees instead of kilometeres. Use ST_Transform to reproject your data into a CRS that uses metric units.



Top 50 recent answers are included