Tag Info

New answers tagged

2

SELECT ST_Intersects(a.geom, b.geom) FROM (SELECT ST_SetSrid(ST_MakePoint(300000, 300000), 3587) as geom) a, (SELECT ST_SetSrid(ST_MakePoint(300000, 300000), 900913) as geom) b; returns ERROR: Operation on mixed SRID geometries, ie, even though these are identical points, and the SRIDs 900913 and 3857 describe identical coordinate reference systems, ...


0

AFAIK you want to use a regular GIST index (PostGIS has a special R-Tree index on top of GIST). Usually it's easier to have one column represent your lat/longs. See http://postgis.net/docs/manual-1.5/ch04.html#PostGIS_Geography For indexed radius search see ST_DWithin


0

First, for polygons, you want the outer boundary of the polygon as a line using ST_Boundary. Then you want a combination of ST_Intersection and ST_SymDifference So something like (untested): WITH firststep AS( SELECT id, ST_BOUNDARY(geom) AS boundary FROM polygons) SELECT p.id, ST_Intersection(boundary, geom), o.id FROM firststep p INNER JOIN othergeoms o ...


2

You are looking for ST_Reclass. There are three types of function signature, essentially differing on how you pass the reclassargs, which is essentially a textual encoding for mapping input to output pixel ranges. UPDATE bin_rast SET reclass_rast = ST_Reclass(bin_rast, 1, '0:-999, 1:100', '8BUI', 0) WHERE rid = 1; where obviously you will need to put in ...


0

In postgis: Group your highways by name and/or by relations membership. Create MULTILINESTRINGs from such groups Use http://postgis.net/docs/manual-2.0/ST_LineMerge.html But still there could be some problems, for example with dual carriageway roads, holes caused by unnamed segments and tree-like road networks.


2

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.


3

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


0

If you want a little more control and a little more SQL: UPDATE TABLE foo SET geom = ST_TRANSFORM(geom, 3857); The command updates the geom geometry column of your table foo with a geometry with transformed SRID.


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

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


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


2

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


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


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


0

You could also try the Full Text Search PostgreSQL capabilities and that would still match your architecture (OL, GeoServer and PostgreSQL). Here you have a very nice resource on PostgreSQL FTS: http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/ According to it, you should build a GiST (and not a GIN) index because it works better ...


2

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


0

I've been asked for this twice now, so sorry for the delay. This is unlikely to be considered a terse solution; I wrote it when a little bit further down the learning curve than I currently am. Any tips welcome, even stylistic ones. --Inputs: --walkingNetwork = Line features representing edges pedestrians can walk on --stops = Bus stops --NOTE: stops.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.


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


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


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

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


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

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


0

CREATE OR REPLACE FUNCTION create_centroid() RETURNS TRIGGER $$ 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, st_centroid(NEW.the_geom_webmercator)); END; $$ ...


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

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


0

There are several ways: http://taginfo.openstreetmap.org already provides this kind of data for the complete OSM data set the tool osmfilter can be used with the parameter --out-count or --out-key to get this data from your OSM data file. you can configure the .style file to just store any tag present in the data set using Postgres' hstore feature. This ...


8

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


0

It seems I have found a good performing SQL SELECT k.rs, k.gen, (ck.incoming-sums.incoming) AS incoming, (ck.outgoing-sums.outgoing) AS outgoing, (ck.within-sums.within) AS within, ST_AsEWKB(ST_Difference(k.geom, geo.geom)) AS geom_b FROM de_commuter_kreise ck INNER JOIN ( ...



Top 50 recent answers are included