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

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


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


7

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.


7

Overlay operators like Contains, Intersection, SymDifference, etc. require exact noding. This is because there are floating point differences on the order of 1e-14 when interpolations are performed between two lines that are not equal. And while 1e-14 is a tiny number, it is still not zero. Use ST_Snap to help "snap" vertices from one geometry onto another ...


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

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


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

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

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


5

ST_Area will use the units in your coordinate system, which as you say are degrees. So you have two options: transform your geometries to a coordinate system that uses feet or meters cast your geometries as geographies. ST_Area will then perform the calculation in meters. You then need to convert from square feet or square meters to square miles. Your ...


5

By using CTEs you are forcing the planner to evaluate each CTE component without looking at any other part of the query. Once you've extracted the results from the tables using the CTEs the fact that there is an index is lost to the planner. You've created a new, non-indexed relation in the CTE. In general, if you can write logic as a plain join, always do ...


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

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

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