Tag Info

Hot answers tagged

7

ST_Distance actually calculates the distance between all the pairs of points, so, as such, no index could be used. So your query will do a sequence scan and then choose those geometries that are less than the distance you specify away. You are looking for ST_DWithin, which does use an index. SELECT SUM(population) FROM points WHERE ST_DWithin(location, ...


4

I found the solution here: http://postgis.net/docs/UpdateGeometrySRID.html --This will change the srid of the roads table to 3857 from whatever it was before UpdateGeometrySRID(varchar table_name, varchar column_name, integer srid); So for my database the table name was 'planet_osm_line' and the column that contains the srid and geometry is 'way'. the ...


4

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


3

I solved it. I found the RPM's for GDAL, GEOS, and Proj4. They are located here: http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/ Since they are not packaged with PostgreSQL 8.4, you have to install them one by one. I hope this helps someone in future.


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


2

This is a memory allocation problem. Your source and target nodes have high id's and PgRouting tries to allocate the memory based on the highest node id it can find, even if there is only a few edges and nodes in the graph. Dijkstra, drivingDistance and other functions have the same problem. IMHO this is a real problem since you can't select a subgraph ...


2

Do not use the regular "Add PostGIS Layers", it will not show your raster table. The plugin "Load Postgis Raster to QGIS" was not updated to QGIS v.2.0, so it won't work either. In QGIS v.2.6 go to menu Database->DB Manager->DB Manager, set the database connection and you will see the raster table. You can drag and drop it or right click and select Add to ...


2

The function ST_Line_Interpolate_Point will interpolate the x, y and z values, given a line as input and a fraction along the line to interpolate to, eg, SELECT ST_AsText(ST_Line_Interpolate_Point(ST_MakeLine(ST_MakePoint(0,0,0), ST_MakePoint(10, 10, 2)),.5)); returns POINT Z (5 5 1) as you would expect. To create the line, you can use ...


2

I suspect the issue relates to the very real difference existing between geometry objects, existing on a plane map surface, and geography objects, existing on a curved globe surface. It is always more accurate (but slower) to do calculations with geography types on a globe. Projecting features from a globe onto a plane always incurs distortion. Looking at ...


2

The answer of @underdark was close, but does not work, because the destination ID's must be passed as an array. Here is the same example of the workshop using queries instead of passing the ID's directly. SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost( 'SELECT id, source, target, cost FROM edge_table WHERE cost >= 0', ...


2

I think you don't need to set the BOX3D, instead use the following approach. But keep in mind that the 0.00012 is a bit crucial, you have to select it carefully. Don't insert 0 instead as I have noticed some abnormal behavior in the past. Plus, if your CRS is in ESPG:3857 then you have to defined this buffer value not in degrees but meters. with line as ...


1

You're using an outdated tutorial with the new pgRouting 2.0 release, I guess. As the error states, it cannot find the function, because the function has been renamed. I recommend you to look at the pgRouting Workshop. The pgRouting documentation also gives brief examples for every function.


1

You could try something like this SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost( 'SELECT id, source, target, cost FROM edge_table WHERE cost >= 0', (select nodeid FROM origins), (select nodeid FROM destinations), false, false) not tested, might be with or without brackets.


1

Use STDWithin to check if the features are within the desired distance For geography units are in meters and measurement is defaulted to use_spheroid=true The documentation page provides simple examples of the usage of this function. In your case this should work: SELECT key FROM the_table WHERE ST_DWithin( GEOM, ...


1

Assuming your structure is someting like this table A ( id int, location varchar(MAX) ) and the data insert into A VALUES(1, '010100000000000000000024409A99999999193340') then you can (bearing in mind the huge performance penalty mentioned by @Vince ) query your database using something like select * from A where ...


1

Rather than formatting text for WKT, you should directly use the numeric coordinate data since it is faster and lossless: update t1 set the_geom = ST_MakePoint(t1.xcoordinate, t1.ycoordinate); And if you also need to assign an SRID: update t1 set the_geom = ST_SetSRID(ST_MakePoint(t1.xcoordinate, t1.ycoordinate), 4326);


1

I figured it out, It should be like this: update t1 set the_geom=st_geomfromtext('POINT('||t1.xcoordinate||' '||t1.ycoordinate||' )'); It worked fine!!


1

I finally figured it out: The real problem was that I stored the coordinates with latitude as the first value. That is the way Google Earth or Google Maps need the values, but it is WRONG for PostGIS. The coordinates need to be stored as (lon,lat). The way I did it, distances were calculated for the sea near Somalia. :P Now the distance calculations ...


1

The short answer is at production time. The loaders that I am aware of (shp2pgsql, for example) will load the projection (prj), geometry and attributes (dbf), but things like data and file owner are not part of the shp spec, directly, so there is no way for a loader to know about them. For tiffs, if you look at the source code for raster2pgsql, there is ...


1

Create the procedural function as CREATE OR REPLACE create_centroid() RETURNS TRIGGER AS $body$ BEGIN INSERT INTO csg.stats (date_needed, date_requested, notice_period, project_name, project_reference, the_geom_webmercator) VALUES (NEW.date_needed, NEW.date_requested, NEW.notice_period, NEW.project_name, NEW.project_reference, ...


1

In general, there is more support for the geometry than the geography data type, see function comparison matrix. For your type of query, classic point in polygon, I think you can use the geometry datatype, setting the spatial reference ID (SRID) to 4326 (lat/lon). See, this thread on the difference between geography and geometry for more info. The gist ...


1

OpenJUMP has a Convert selected geometries/layers function that does exactly that. Question is if that is the intended result. See the example below:


1

Apparently, I don't have enough points to add a comment so I am using this Answer just to say that I tried both ST_MakeEnvelope vs the maths compare of "x > min_x and x < max_x and y > min_y and y < max_y" ...on average ST_MakeEnvelope took 60ms and maths compare took 155ms on my particular bbox query. So the spatial search ST_MakeEnvelope should be ...



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