Tag Info

New answers tagged

0

Your query looks fine to me - are you certain of your point coordinates? The geometry produced by "ST_GeomFromText('POINT(40.587840 22.951521)',4326)" is in Saudi Arabia. Is this what you expect?


1

ST_Dump is returning multiple values for one row, which cannot be nested within the ST_Collect() function, so you must separate things out using a sub-query. SELECT ST_Collect(the_geom2) FROM ( SELECT (ST_DumpPoints(the_geom)).geom AS the_geom2 FROM test_line) foo;


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


2

You are currently only querying the points table. Railway stations might as well be mapped as polygons. A good strategy to get all commonly used OSM tags is to display your data on top of an openstreetmap background (e.g. with the openlayers plugin in QGIS), and look for missing stations. You can install the JOSM editor, zoom to the missing object, ...


1

Looking at http://wiki.openstreetmap.org/wiki/Tag:railway%3Dstation it seems that the new tag is public_transport=station though it is hard to see if that is a recent change or not.


0

Ok. Since apparently nobody knows how to do this, I wrote a small nodejs program that performs the task: tags2nodes. You have to specify the osm input file, the relation you want to filter, the postgres user and db data and the program creates a table to store the ouput nodes with 3 columns: id, tags, and geometry. In the column tags (json stringified by ...


1

The simplest way is to use ST_GeometryType. In your case, to check for polygons only, you can do, SELECT ST_Intersection(polygon1, polygon2) FROM ..... WHERE ST_GeometryType(ST_Intersection(polygon1, polygon2)) = 'ST_Polygon'; For more complicated scenarios, you could also look at ST_Relate and the DE-9IM model, but I think that is probably overkill for ...


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


6

You've got to grant permissions on each of the sequences in addition to the tables, like so: GRANT ALL ON TABLE [SCHEMA].TRACK_ID_SEQ TO [USER_GROUP];


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

It seems your "key" field must have type integer and not text. I reproduced your problem with the following snippet: uri = QgsDataSourceURI() uri.setConnection("localhost", "5432", "mydb", "user", "pass") query="SELECT id, CAST (id as text)|| '_' || (ST_DumpPoints(geom)).path[2] as key, (ST_DumpPoints(geom)).geom as points FROM line" uri.setDataSource("", ...


1

In order to use this data in PostGIS or another system you need to convert the start-center-end arcs into start-midpoint-end arcs. This is actually pretty easy. In vector terms, you subtract the center from the start- and end-points. Now you can get the mid-point of the arc by adding the start- and end-points together, then normalizing to the radius of ...


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.


1

There are a series of projections suited to this exact situation, 10TM, 6TM and 3TM. They are essentially Transverse Mercator slices with custom central meridians, spanning 10 degrees, 6 degrees, or 3 degrees of longitude, respectively. I first encountered them working on data within the City of Calgary, which straddles two UTM zones. A reference: ...


2

If you are using Django (GeoDjango), look at vectorformats, which will produce GeoJSON that you can use directly in Google Maps -- we are using this in production, on a decent sized database, millions or rows and complex polygons, and it works really well. If you want to query Postgis directly, use ST_AsGeoJSON as @underdark says, but note, you need to do a ...


1

Don't code, use ST_AsGeoJSON — Return the geometry as a GeoJSON element.


1

If you run a billion of them you might see the difference between the overhead of parsing a text box vs directly constructing the geometry from the envelope coordinates. Do whatever you like the most and find the most convenient.


1

Maybe try something like this? http://docs.geotools.org/stable/userguide/library/jts/geometry.html#creating-circularstring


1

First of all, ST_Distance_Sphere returns in meters, so you are actually looking at 8km, which might be more reasonable. I suspect, also, that you have you lat/lon the wrong way round -- your point is somewhere off the coast of the Seychelles, not Carlisle, and there are not too many roads in the Indian Ocean. Also, while the query planner will optimize this ...


0

After a lot of thinking and research here, I believe I found a solution. I would love to receive some peer review and feedback on this approach. I used ogr2ogr to generate a GeoJSON file from a PostGIS query using the CIRCULARSTRING function. ogr2ogr -s_srs EPSG:26910 -t_srs EPSG:26910 -f GeoJSON testArc.geojson PG:"host=localhost ...


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

Given Paul Ramsey's excellent explanation of why the next question is what can be done about it. How do you SELECT DISTINCT on geometry fields and have it perform as expected? In Paul's answer, I proposed using SELECT MAX(geom) FROM the_table GROUP BY ST_AsBinary(geom); but MAX() is slow, apparently requiring a table scan. Instead, I found this to be ...


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

There are packages created for MacOS, usually by KingChaos. Looks like you can also use homebrew: http://www.kyngchaos.com/software/postgres http://brew.sh/ As usual, the best way is to look at the docs, as already said by John, check this link: http://postgis.net/install


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


3

You need ST_Difference which returns the the geometry of a feature that does not intersect with another feature. In your case something like this: SELECT l.id, ST_Difference(l.geom, p.wkb_geometry) As diff_geom FROM lines l, polygons p Modify for your actual table and field names.


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


3

I think ST_Difference is what you are looking for.


3

I had a similar problem and this mailing list post helped me: http://lists.osgeo.org/pipermail/postgis-users/2010-June/026887.html The solution they came up with is as follows: SELECT gid, ST_AsText(replace(ST_AsEWKT(geom), 'LINESTRING', 'MULTIPOINT')::geometry) FROM (SELECT 1 as gid, 'LINESTRING (0 0, 0 3, 3 4)'::geometry AS geom UNION ALL SELECT 2 ...


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


0

I've been having the same problem for a few days now. I found a solution, however, I didn't use PostGIS. Instead, I used QGIS (2.0.1 - Dufour) to do so. My problem was the same as yours. I imported a shapefile of road network that I have previously displayed in QGIS, where it would display correctly. The original SRID was 3347 (EPSG:3347 - NAD83 / ...


4

You don't need constraints for PostGIS 2.x, just us typmods. For 2D geometries with SRID=4326, the typmod is geometry(Geometry,4326). Or for M-dimension geometries, it would be geometry(GeometryM,4326) (you get the idea). Example: create temp table sometable(wkt geometry(Geometry,4326)); -- works insert into sometable(wkt) values('SRID=4326;POINT(1 2)') ...


3

@BradHards is right. Having mixed types is probably going to cause problems down the road. If you're sure your tools can handle mixed types, or you're going to be retrieving them some other way (through DB views, or programatically) you can split the constraints into two parts, either of which is optional: To modify your existing table -- Only check the ...


2

I would prefer EPSG:3035. The ESRI codes might not be available on all platforms: EPSG:2163 has a different lon_0 leading to a distortion of the map:


1

Actually it would work as both are about ~~~~ the same latitiude but use the Euro version http://spatialreference.org/ref/esri/102013/


4

You could calculate ST_Area on a geography type. Since you have data with WGS84 (SRID=4326), you can add a simple geography cast, e.g. SELECT ST_Area(geom::geography) which will return area in m² on a curved surface (sphereoid by default). This should be pretty close to the true surface area, without requiring any projection. It would be interesting to ...


3

ST_DWithin uses units which match your geometry's CRS. Since your geometry has a CRS of 4326 the distances used will be in degrees - not very helpful! However, if you use 'geography' types rather than 'geometry', then ST_DWithin always uses metres for distance. So, you'll need to first convert your geometry into a geography type, so that the measurements ...


1

I also recommend TRANUS. Its a complete land use model and a detailed transport model, in which modal split and assignment are in one integrated process based on logit. It may be applied to cities or regions alike, combining passengers and freight. Download programs and extensive documentation from www.tranus.com. There are ways to communicate TRANUS with ...


1

EDIT There is a plpgsql function is this post that splits a Line using multiple points. However, it can also be done in a query, using generate_series, ST_Line_Locate_Point and ST_GeometryN to get knife points and ST_Line_Substring to split the line. Whichever you prefer, I leave up to you, as this query is not exactly terse. CREATE TABLE sometable AS ...


2

Here was my problem: No way the SRTM data is that messed up. The SRTM data IS that messed up. The warping above is actually in the DEMs in the SRTM3 dataset (downloaded from http://dds.cr.usgs.gov). After examining DEMs from the improved SRTM4 dataset (available here) I found that most of these "gaps" were filled by interpolation but other issues ...


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

Possible Solution: Tuning PostgreSQL As I said before in the comments, I had the problem of having the I/O on my SSD maxed out, blocking the System. I tuned PostgreSQL with the following config file and it works much better for me now. Best is to copy paste the code into a new file, place it where the postgres.conf is located (Gentoo /etc/postgresql-9.3/) ...


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



Top 50 recent answers are included