Hot answers tagged

10

Be aware that the GeoJSON specification states that The GeoJSON object must have a member with the name "type". This member's value is a string that determines the type of the GeoJSON object. http://geojson.org/geojson-spec.html#geojson-objects If you decide that you do not want/need to be compliant with the GeoJSON spec, you can use the - (minus) ...


9

I suspect you are running the latest PostgreSQL Patch released yesterday. I have confirmed an incompatiblity issue with latest release OpenSSL and the libcurl we ship - detailed here - http://www.postgresonline.com/journal/archives/364-PostGIS-2.2-Windows-users-hold-off-on-installing-latest-PostgreSQL-patch-release.html We have compiled a new version of ...


7

You could write a before-delete trigger and modify your insert and update-trigger like my following example. The workflow works so far but the code could still be "cleaned" and optimized to prevent recursions between the different triggers... so I post my code as "work in progress" ;) https://gist.github.com/neogis-de/a1d08c38d8b9c5d316c7 CREATE TABLE ...


7

The osm2pgsql documentation suggests osm2pgsql -c -d gis --slim -C <cache size> --flat-nodes <flat nodes> planet-latest.osm.pbf. where <cache size> is 24000 on machines with 32GiB or more RAM or about 75% of memory in MiB on machines with less <flat nodes> is a location where a 24GiB file can be saved. This works, and on a fast ...


7

Here is a proposed cleaned-up code. Assuming you need to input the buffer size in the local unit of "EPSG:26986". To simplify a bit, I propose to intersect geometry directly into the local SRID. This make the query more generic because it will work no matter what is the input SRID of the geometry (if its correctly defined!). And if the SRID (4326) is ...


7

Here are a couple of items to explore to determine why ArcGIS is not registering your PostGIS tables as feature classes: ArcGIS will refuse to acknowledge tables that have mixed geometry types. To clarify, "POLYGON" and "MULTIPOLYGON" types can be mixed within the same column, but POLYGON and POINT cannot. In your case, coming from a shapefile, this is not ...


6

There is simple query for this case. SELECT a.id AS store,count(b.*) AS customer_count FROM stores a, customers b WHERE ST_DWithin(a.geom::geography,b.geom::geography,5000) GROUP BY a.id I have stored my sample data in the projection WGS 84 (4326). When you want to use a metric system, you have to convert the geometries to geography format. The ...


6

If you only want Polygons or Multipolygons from ST_MakeValid you can use ST_Dump to extract the constituent geometries and then test for the geometry type. ST_MakeValid will sometimes produce Points or LineStrings which is where the GeometryCollection is coming from. Try something like: SELECT g.geom, row_number() over() AS gid, FROM (SELECT ...


6

First use a window function to get the ordered rank of the dem_points. In a second step filter the dem_point with the lowest dn by the rank. SELECT osm_id, gid, dn FROM ( SELECT b.osm_id, p.gid, p.dn, row_number() OVER (PARTITION BY osm_id order by dn) as rank FROM buffer b, dem_points p WHERE ...


6

If you can updgrade to postgis 2.2.0 you might be lucky because that feature has just been introduced. From the doc: "ST_ClusterWithin is an aggregate function that returns an array of GeometryCollections, where each GeometryCollection represents a set of geometries separated by no more than the specified distance." Check this: ...


6

The question has indeed been asked before, but it is still a good question :) The manual already gives you a hint towards the problem. The main problem is that you are trying to use geograpy types (that work with real world coordinates) while the function st_intersection is not completely ready for geography. In the real (spherical) world, your geometries ...


6

select st_asgeojson(st_transform(ST_SetSRID(geom, XXXX), 4326)) AS geojson FROM ptable where level_1='Built Up' Try setting SRID beforehand by replacing XXXX with the code of your SRID.


5

ST_Polygonize will do the job: CREATE VIEW boundarypolygons AS SELECT g.path[1] as gid, g.geom::geometry(polygon, 31492) as geom FROM (SELECT (ST_Dump(ST_Polygonize(geom))).* FROM boundary ) as g;


5

It could be faster, you're right but what you really need if you don't already have one is a spatial index. This will then be able to do a first pass of your query quickly by working out which bits of the data the query needs to look it. It will then check these data points for which fall exactly within the polygon. You can create an index using information ...


5

That first ST_SetSRID in your subquerry should be ST_Transform. With ST_SetSRID you just assign new SRID to the geometry without actually transforming your data into the new SRID.


5

Assuming you are using geography type which I am guessing from geog name, query to find all census blocks within 1609 meters ~ 1 mile) of parcel denoted by key '12345'. SELECT census_blocks.* FROM census_blocks INNER JOIN parcels ON ST_DWithin(census_blocks.geog, parcels.geog, 1609) WHERE parcels.parcel_id = '12345';


5

QGIS has several ways to import OSM data: using Layer -> Add Layer -> Add vector layer using Vector -> OpenStreetmap Both create a spatialite database. You can take the layers of the spatialite database and export them to an empty Postgis database. But using om2pgsql is the far better tool, because it can deal with large amounts of data far ...


5

The column format for PostGIS is called geometry and you'll probably need to read up on all the functions associated with managing spatial columns. If you are getting the lat/longs from GPS or Google, the projection will be EPSG:4326. edit: iant's comment brings up a good point about the geography type, and indeed if you are using EPSG:4326 it looks like ...


5

PostGIS lat-lon This problem agonized me for a while, so I wrote PostGIS lat-lon. It converts any geometry to a lat-lon pair, a TSV tuple or a Google / Bing Link. In OSX console, links are clickable, so no copy-paste is required. Usage Example: SELECT latlon(geom), latlon_parens(geom), latlon_tab(geom), latlon_google(geom), ...


5

My first attempt on this topic will be something like this. UPDATE poi SET name_displ = regexp_replace(name_displ, '([A-Z]{1}[a-zA-Z]{2}\s)', upper('\1')); Where the key points are: When you want to make a "substitution" you must do an UPDATE query To get the text match by the regexp you use \1. This is you can use '\1' to use in the substitution the ...


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

It appears you were/are on the right track as the 'Z' is used for a timezone indicator, here is the source I found that best answers your question "Z" is kind of a unique case for DateTimes. The literal "Z" is actually part of the ISO 8601 datetime standard for UTC times. When "Z" (Zulu) is tacked on the end of a time, it indicates that that time is UTC, ...


4

You can render your data from PostgreSQL directly. Documentation here. PostgreSQL layer example: LAYER NAME "province" STATUS ON TYPE POLYGON CONNECTIONTYPE POSTGIS CONNECTION "host=127.0.0.1 port=5432 dbname=xyz user=postgres password=postgres" DATA "the_geom from xyz" CLASS STYLE COLOR 128 128 128 ANTIALIAS true END ...


4

The solution arrived in PostGIS 2.0, ST_IsValid() and ST_MakeValid(). You can ask PostGIS to fix broken geometries. You must be careful with it tough it the source geometry's integrity is important for some reason.


4

I know this is too late for the OP, but hopefully it will help someone like me who came accross this question. I worked through several issues trying to use a postgis raster in geoserver similar to the OP, the below is how I succesfully loaded by raster data. Preconfiguration. A BNG geotiff layer, generated from FME, has been imported into postgis using ...


4

You can go with the SQLExecuter, the initiator would need to be a list of database values [keys] you want to delete. Then using something similar the SQL statement below to execute the delete query should work. DELETE FROM table WHERE key = '@Value(key)'; Or you can use the DatabaseDeleter


4

I am using postGIS sql to split feature by line in JAVA, and my code worked. my code is: public List splitGeometry(String geom1, String geom2) { List<String> result=new ArrayList<String>() ; try { Statement s = connection.createStatement(); String sql_stat = null; sql_stat = "select st_astext (a.geom)from ...


4

You could create function something like this: create or replace function ST_MultiSplit(geom Geometry, blades Geometry) RETURNS Geometry AS $$ BEGIN FOR i IN 1..ST_NumGeometries(blades) LOOP geom = ST_Split(geom, ST_GeometryN(blades, i)); END LOOP; RETURN geom; END; $$ LANGUAGE plpgsql; Then use it like: Select ST_AsEWKT(a.geom) ...


4

Postgis is an extension to Postgres, rather than a stand alone application, that provides a spatial data type to Postgres, and provides numerous spatial functions that operate on geometry(ies). Spatial indexing, which you will surely need to find n closest points efficiently, is implemented as an extended R-tree, but the indexing mechanism comes from ...


4

You can bind a trigger function to as many tables as you want; just execute a CREATE TRIGGER statement for each binding. Make sure to schema-qualify the table name in your statement (BEFORE INSERT OR UPDATE ON a.point etc.) If you have a large number of schemas, you could generate the SQL dynamically by iterating over the rows in ...



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