Hot answers tagged

15

My guess is that you coordinate transformations have introduced tiny rounding errors (see an example below). As there is no way to set the tolerance in ST_Equals, this is causing ST_Equals to return false for some geometries that only differ in the nth decimal place, as the geometries have to be identical in every respect -- see the intersection matrix ...


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


7

Ironically, the fastest way to find the set of things not within other things is to do a full join that finds the contained things, but using a LEFT JOIN, so the un-matched things are hanging about to be found, thus: SELECT pts.* FROM pts LEFT JOIN polys ON ST_Contains(polys.geom, pts.geom) WHERE polys.id IS NULL; The un-matched rows in a left join are ...


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


6

Save your Excel as a comma-delimited file, then: COPY your_table FROM '/path/to/csv/file/data.csv' WITH DELIMITER ',' CSV HEADER; Another option would be ogr2ogr. Edit: after some more searching, the answers to this question should help.


6

Assuming you have at least PostgreSQL version 9.3, you can use a few JSON functions and operators to extract the relevant parts of the GeoJSON specification required by ST_GeomFromGeoJSON to create geometries. Try the following, where you can replace the JSON in the top part: WITH data AS (SELECT '{ "type": "FeatureCollection", "features": [ { ...


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

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


5

You can do this using a Postgres DO block, essentially an anonymous function that you just run once from a psql prompt. Something along these lines: DO $$declare r record; BEGIN FOR r in SELECT srid as srs, f_table_name as name, f_geometry_column as geom FROM geometry_columns WHERE f_table_schema = 'schema_name' LOOP RAISE NOTICE ...


5

If that is the query that is used I guess you are out of luck. Is there no WHERE-clause? I have never played with cursors, but as I understand the query, Arc-whatever fetches 1000 rows a time from the whole data set. No matter how efficient and smart Arc-whatever then is to find out what geometries to use and render. All performance is already lost. ...


5

Here's what I do for Microsoft Excel files: Preprocess the source Excel Worksheet to ensure that the headers are on the first row, and all data from row 2 and downwards look normal. This may need to be revised if the next step has difficulties Start Microsoft Access and create a Blank database. Navigate to "External Data", and choose to Import Excel, and ...


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

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


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

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


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


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

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

I believe the general outline (partly tested so far) is: Find the points representing stream sources, as in this answer. Intersect with the polygons table to get a count of source vertices by polygon. Use ST_DumpPoints in conjunction with group by geometry to get a count of each point. The idea being to get a count of how many rivers meet at a given point. ...


4

You have to convert your geometry column to WKT to get the point (or any geometry) as text format. http://postgis.refractions.net/documentation/manual-1.4/ST_AsText.html cursor.execute('SELECT ST_AsText(geometry) from test') Probably something in ppygis is missing, because it seems like it converts the point geometry to the PostGis ST_Geometry format but ...


4

Behrman is special case of cylindrical equal area. There is some information about this as well as a warning about an issue in some old Proj4 versions in this thread http://groups.google.com/forum/#!topic/oracle_ugent/2WUt9HmcrHU. Proj4 string to use is +proj=cea +lon_0=0 +lat_ts=30 +x_0=0 +y_0=0 +datum=WGS84 +ellps=WGS84 +units=m +no_defs


4

If you have global data, use a geography type: CREATE TABLE points ( gid serial primary key, name character varying, point geography(Point,4326) ); CREATE INDEX points_point_idx ON points USING gist (point); Then use a function that can use the spatial index, using a metric distance (see ST_DWithin) SELECT name, point FROM points, (SELECT ...


4

ST_Collect is probably not the function you are looking for, as this simply combines geometries into a geometry collection of some type, and does not actually union/dissolve them. ST_Union, on the other hand, does dissolve overlaps, and assuming polygonal input (which is most probable in conjunction with ST_Buffer and an input table called point), and ...


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



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