Hot answers tagged

7

Use ST_GeomFromGeoJSON. SELECT ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[-125045.48351212002,4577567.588141698],[-124816.19981552364,4577552.93014355],[-124765.99472517562,4577419.175847012],[-124842.47121534991,4577392.905406596],[-125045.48351212002,4577567.588141698]]]]}'); Returns: ...


6

Use ST_GeomFromGeoJSON select ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[-125045.48351212002,4577567.588141698],[-124816.19981552364,4577552.93014355],[-124765.99472517562,4577419.175847012],[-124842.47121534991,4577392.905406596],[-125045.48351212002,4577567.588141698]]]]}') Result: ...


5

Your GeoJSON sample lacks information about the coordinate reference system, although you say it is SRID 900913. This is so-called Google Mercator or Web Mercator, which has been superseded by EPSG:3857, which I will use in this example. An important question is whether you really want Well-Known Binary, which does not include an SRID, or Extended Well-Known ...


5

If you have imported a planet or extract some time ago and have now downloaded a (much) newer planet or extract: It does not really make sense to do any updating as I think calculating and applying the diff will not save you time. Just re-run osm2pgsql again and it will remove the tables and create new ones resulting in updated data. If you want to keep ...


4

If you want to transform coordinates to another SRID, you will use ST_Transform: SELECT ST_AsText( ST_Transform('010600002031BF0D0001000000010300000001000000050000008FEF9C07089AFEC0B90A9856E87251410F355DB1B395FEC0DCCEADDCED72514194BE3130A693FEC0DFD23127D072514114797186FA97FEC094E0C313CB7251418FEF9C07089AFEC0B90A9856E8725141' ...


3

The number width/precision is specified in the DBF file, as support by GDAL/OGR with get/set width/precision (i.e. see API). Looking at the documentation for the PostgreSQL / PostGIS driver, there is a PRECISION layer creation option: This may be "YES" to force new fields created on this layer to try and represent the width and precision information, if ...


3

YES The -spat switch is completely optional, and the typical use case is probably not to use it. I never use it, because when doing a shapefile import I have never needed only part of the shapefile (or I would rather get it all into PostGIS and then do a spatial selection there). ogr2ogr will run and import all features in the shapefile. Your statement ...


2

Similar to what Zoltan has said, but you want to group by the color, which will then get you the sums for each color. SELECT sum(ST_Area(ST_Intersection(grid.geom, affected.geom))), grid.color FROM grid, affected WHERE ST_Intersects(grid.geom, affected.geom) GROUP BY grid.color; The final WHERE ST_Intersects(...) will not affect the answer, ...


2

SELECT sum(st_area(st_intersection(grid.geom, affected.geom))) FROM grid, affected WHERE grid.color = 'green' and st_instersects(grid.geom, affected.geom);


2

By default shp2pgsql does NOT create indexes. You need to pass -I to make it generate a spatial index. http://postgis.net/docs/manual-1.3/ch04.html#id435762 Check if your table has an index by running \d tablename in psql. In the list of indexes should be a line with "gist" (unless you picked a different index) and your geometry column name. You can add ...


2

Could be, for example: SELECT kph, count(*) FROM data WHERE ST_Within( ST_Transform(geom, 2163), ST_Buffer( ST_Transform( ST_GeomFromText('LINESTRING (77.2089421749115 28.5225030428734, 77.2088992595673 28.5223333626164,77.1612417697906 28.5640959203597)', 4326), 2163), ...


2

Instead of a marker you can generate a buffer, setting each category (metro, tam and bus) to a numeric field (1000, 750 and 500). Then run the following queries: SELECT cartodb_id, ST_Transform( ST_Buffer(the_geom::geography, 1000)::geometry ,3857 ) AS the_geom_webmercator FROM table_name WHERE field_name ilike 'metro'; SELECT ...


2

You need to define how the tables included are related. Also, for this purpose you have st_shortestline, that creates the line for you. The syntax is: Select st_shortestline(a.geom, b.geom) From a, b Then you probably want to put some where clause there or you will get all possible combinations between the tables.


2

So far I could think of computing the bounding box and getting its maximum Y value like in: order by ST_YMax(Box2D(geom)) PS: Actually the conversion to a box is not necessary and to sort from north to south desc works like a charm, hence the final query part is order by ST_YMax(geom) desc


2

ogr2ogr/ogrinfo are great tools...but also...PostgreSQL provides two built-in tools for exporting (pgsql2shp) and loading (shp2pgsql) shapefiles. They are easy to use and well documented...see the docs and this cheat sheet. Quoting the PostGIS 2.0 docs: An example session using the loader to create an input file and uploading it might look like this: ...


2

This can be done a bit more simply with json_build_object in PostgreSQL 9.4+, which lets you build up a JSON by supplying alternating key/value arguments. For example: SELECT json_build_object( 'type', 'Feature', 'id', gid, 'geometry', ST_AsGeoJSON(geom), 'properties', json_build_object( 'feat_type' : feat_type, ...


1

You cannot simply print a class. If you want to do so, you have to add a toString() method to it. See e.g. http://stackoverflow.com/questions/8555771/why-is-the-tostring-method-being-called-when-i-print-an-object or http://stackoverflow.com/questions/8001664/how-to-create-a-println-print-method-for-a-custom-class or ...


1

First, using <#> will only compare bounding boxes. If you have PostGIS 2.2 (or greater in the future) you can use <-> operator to get the real distance calculation. That means that after the index search a recheck is done to find out the real distance. Second, as John B commented, the knn-functions need a constant to work with. Since you are building ...


1

I started it all again: restored a previous database from backup; create extension postgis; create extension postgis_topology; ogr2ogr from console creates additional tables (like waypoints); after this I need to grant access in those additional tables to php/apache user.


1

You can use the ST_AsText function to get the WKT representation of your geometry, this will return the coordinates in whatever projection you have. (You will only get the latitude and longitude if your data is stored in a Geographic Coordinate system, SRID 4326 works for most cases) You can use this query to change SRID ALTER TABLE table     ALTER COLUMN ...


1

I think you're overcomplicating things here when you could use ST_MaxDistance, this will return the maximum distance between two points within two point clouds. WITH points AS( SELECT id, (ST_DumpPoints(lane.geometry)).geom as geom FROM lane GROUP BY id ) , min_distances AS( /*Finds the smallest distance for each distinct tuple ...



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