Tag Info

Hot answers tagged

3

I think you want to exclude the intersection of the buffer in the where clause. WITH subq AS ( SELECT p.id, p.name, unnest(ARRAY(SELECT q.name FROM w_point q WHERE p.id != q.id AND NOT ST_Intersects(q.geom, ST_Buffer(p.geom, 0.1)) ORDER BY ST_Buffer(p.geom, 0.1) <#> q.geom LIMIT 5) ) as name FROM w_point p ) SELECT ...


3

You can simplify the whole thing by using coalesce, which selects the first non-null item in a list, in this case, either the nearest point to you search point, (28.959495,41.019913), or the point itself. with input_geom (geom) as (select st_setsrid(st_makepoint(28.959495,41.019913), 4326)) select coalesce( (select st_closestpoint(pts.the_geom, ...


2

The shapefile you provided seems to be valid, and the projection in the prj files states (if im not mistaken) that your coordinates of each vertex are tied to WGS84 (EPSG:4326) Using ogr2ogr PGDump driver you can translate your shapefile to a sql querry that can be used to insert data into the database: If you are in Windows you can open the console with ...


2

In general, the size of the geometries won't make much difference to the indexing performance - the R*Tree is hierarchical, and can deal with very different sizes. What is going to make a difference is the number of hits that aren't filtered by the index. That depends on what type of queries you perform. For example, if you have a lot of point-in-polygon ...


2

Use a self-join of the same table twice with different alias. To find the intersection of one linestring with all others: SELECT b.id FROM trajectory a, trajectory b WHERE a.id = %s AND a.id <> b.id AND ST_Intersects(a.line_geometry, b.line_geometry) And to find all combinations of intersections of linestrings in the table: SELECT a.id || ' ...


1

This: 'POINT(?, ?)' is within a literal string, so placeholders are not interpreted. You must instead keep the placeholders outside a literal and use concatenation or function call syntax. In this case there's actually a point(x,y) function, so you can just write: POINT(?,?) without the single quotes, but if that weren't the case and you needed to ...


1

I believe adding the osm user name or osm user id is out of the design scope of imposm. I don't know if the design goals are any different for the rewrite of the tool in Google's go language. Imposm wants to be fast at helping you create maps and does not appear to be interested in other data that may be used for statics or does not add to map data. Most ...


1

Your friend here is ST_LineInterpolatePoint. Look here for more information: http://postgis.net/docs/ST_Line_Interpolate_Point.html. You have to compute the right fraction to get your 10 metres. So this is done with this little calculcation: 10 metres = ST_Length(the_line) * fraction fraction = 10 metres / ST_Length(the_line) So the result is (like the ...


1

ST_LineInterpolatePoint(the_line, 10/ST_Length(the_line))


1

Lots of questions cause your question is too broad and subject is not as simple as it seems... Writing complete resolution for you could take few hours (!!!) so I'll only write down my line of thinking, it should give you direction which way to go. You have to achieve road network data to count routes... OSM should be good enough. Convert data into graph ...


1

Just wish to add two things to John Barça's fine answer: First, ST_PointFromText() would be most useful if your coordinate data were already in the form of lines of text like this POINT(xxx.xx yyy.yy) where each xxx.xx yyy.yy were actual coordinates, perhaps as output from another process/system. Going out of your way to add in the text "POINT" is ... ...


1

Are you calling pgr_drivingdistance and then using id2 (the "edge id") to join back to your edge table? If so, the pgr_drivingdistance function actually currently has a bug which causes this 'edge id' to not represent the actual path taken to get to that node. see https://github.com/pgRouting/pgrouting/issues/203 There's currently an open pull request to ...



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