Tag Info

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

ST_Distance actually calculates the distance between all the pairs of points, so, as such, no index could be used. So your query will do a sequence scan and then choose those geometries that are less than the distance you specify away. You are looking for ST_DWithin, which does use an index. SELECT SUM(population) FROM points WHERE ST_DWithin(location, ...


8

In Ubuntu 14.04 you also need to install the postgresql-9.3-postgis-scripts package. After I ran sudo apt-get install postgis postgresql-9.3-postgis-scripts I was then able to successfully run CREATE EXTENSION postgis; in my database to initialise PostGIS.


7

Why use a database? Because it's not necessarily the case, especially with larger datasets, that you can expect to be able to push the entire thing to the client. If you're talking thousands of points, then sure, but for millions of points you probably don't want each and every one represented in RAM on your end users' client. Not everyone has a super fast ...


6

A really easy, but not fantastic measure is to get the Hausdorff distance between each combination, which is done with the ST_HausdorffDistance function. Using approximate LineStrings from your figure, these are all shown in blue, and the Hausdorff distance is shown for one of the pairs of lines in red: And the query to sort the 6 combinations in ...


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

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


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


5

Since you're not worried about simultaneous edits on features, I'd say that in theory you have nothing to worry about. The main danger w/ QGIS is that simultaneous editors can stomp on each other's edits without noticing ("last edit wins"). For data under active editing with multiple users you might want to at least keep track of history, which you can do ...


5

If you don't have an attribute, you should first find a polygon feature class with the boundaries of Greece (e.g. on gadm.org). Then you have two solutions : You want the roads to be cut at the boundaries of you area of interest : use some clip tools (in QGIS : Vector -> Geoprocessing -> Clip ) You want to keep the two sides of a road when it crosses a ...


5

Your data looks like it's Mercator (big negative X's). I'll assume web mercator, but that could be wrong, and may lead to meter-level inaccuracies (if it's "real" mercator, use 3395). shp2pgsql -c -d -D -W LATIN1 -s 3857 -I 'path/to/shapefile.shp' my_table | psql -d mydb Now flip the coordinates to geographics inside the database ALTER TABLE my_table ...


5

First of all, some performance metrics, comparing the two different ways of producing points for a random selection of a million points. create table test (id serial, x real, y real, geom geometry(POINT, 27700)); insert into test (x, y) select random(), random() from generate_series(1, 1000000); update test set geom = ST_SetSRID(ST_MakePoint(x, y),27700); ...


5

You can simplify the whole thing by using coalesce, which selects the first non-null item in a list, in this case, either the nearest point to you search point, (28.959495,41.019913), or the point itself. with input_geom (geom) as (select st_setsrid(st_makepoint(28.959495,41.019913), 4326)) select coalesce( (select st_closestpoint(pts.the_geom, ...


5

ST_3DIntersection is available only via the SFCGAL extension. Unfortunately only windows (PostGIS 2.2 experimental) and Ubuntu (via docker ) have it at the moment, so most likely you'd have to compile PostGIS yourself to use it. It would be useful to know the OS you are on. PostGIS windows 2.2 experimental binaries have SFCGAL and can be downloaded here: ...


5

I suspect we don't support it, however you could try with the -m switch where what follows the -m is the filename consisting of old name and new name. It might not work though since gid is an autogenerated field name. If it doesn't feel free to put in a ticket in our ticket tracker http://trac.osgeo.org/postgis/newticket . i think it's a fairly easy ...


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


4

Figured this out! (also asked here) A record in a spatial table (Shapefile, PostGIS, Spatialite) that has no geometry can have geometry added by adding a 'part' to the feature using the advanced digitizing toolbar. Here's the workflow: Bring table containing the geometry-missing feature into QGIS Select the layer from the layers panel and start an edit ...


4

EDIT: Update, having belatedly realized this is a Map Server question. In general, to get the convex hull, you will want to group by id/gid, as producing the hull(s) is an aggregate operation. So, without the Mapserver part, the query would look like: SELECT id, ST_ConvexHull(ST_Collect(the_geom)) as the_geom from xyz group by id; Now, to get MapServer ...


4

I think you want to exclude the intersection of the buffer in the where clause. WITH subq AS ( SELECT p.id, p.name, unnest(ARRAY(SELECT q.name FROM w_point q WHERE p.id != q.id AND NOT ST_Intersects(q.geom, ST_Buffer(p.geom, 0.1)) ORDER BY ST_Buffer(p.geom, 0.1) <#> q.geom LIMIT 5) ) as name FROM w_point p ) SELECT ...


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


4

Your problem is that you have excluded the public schema from the search_path. All functions and types is in the public schema (by default) So what you have to do is just include the public schema in the search_path SET search_path TO sml, public; means that the database will write to sml if you don't give any schema SET search_path TO public, sml; ...


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


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

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