Tag Info

New answers tagged

0

Hmm as I recall there should be in an sfcgal output in your postgis_full_version() output. Did you compile postgis with sfcgal support? It's not enough to just have sfcgal installed. I see you are right the instructions in docs don't tell you how to compile with sfcgal support. I'll amend that. What you need to add is in your postgis configure ...


5

You no longer need to use the templates or even the script in the new versions of Postgis. As you can see in the Postgis - installation page, all you need is run the CREATE EXTENTION command. -- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- fuzzy matching needed for Tiger CREATE EXTENSION ...


0

I never use it, simply create a new DB from basic Postgresql template: CREATE DATABASE pgis_template TEMPLATE template0; Then i load and run scripts postgis.sql and spatial_ref_sys.sql for your basic setup. I then use this template DB for all the others i create


2

You implicitly CROSS JOINed the tables so you got a cartesian product. Since you don't want to merge the geometries together you should use the UNION ALL which is an entirely different construct: SELECT a.geom FROM the_network a UNION ALL SELECT b.geom FROM buildings_to_network_lines_table b Now that you have a correct SELECT query you can use it in ...


1

You're getting strange results because you have more than one row in purple that joins to orange on the condition ST_Intersects(purple.geom, orange.geom). In PostgreSQL, this produces undefined behaviour. From the PostgreSQL docs on UPDATE: When a FROM clause is present, what essentially happens is that the target table is joined to the tables ...


2

You chose the wrong argument order. This is correct the ST_Contains function ST_contains(bar.hull,ST_GeomFromText('POINT (34.8039 32.1049)'))


2

A "very fast" query in the database against a moderately sized table will take about 15ms. Your test queries are taking 100 times less than that. Why do you think you have a speed problem? I'm pleased everything is as fast as all that, frankly. Yes, doing a proper geodetic distance calculation will take more time than a flat distance calculation on ...


3

According to www.postgis.org/docs/ST_Collect, ST_Collect — Return a specified ST_Geometry value from a collection of other geometries. (emphasis mine) but it seems your location column is of type geography. Perhaps you need to convert the geography to a geometry via casting: St_Collect (location::geometry)


4

What do you mean by line = length? I guess what you mean is that your subquery will find the id of the shortest line. Then you ask in the outer query for a line with that id and some number less than 500. If the shortest line then have a a number that is more than 500 you will get nothing back. EDIT Something like this should work if I understand you ...


0

I was able to force the query plan to use the spatial index by re-writing the query using the WITH expression, as suggested in a related question, PostGIS Intermittent INDEX Performance. Here's the new sql: WITH features AS ( SELECT pname, pid FROM search WHERE gid = '50873' LIMIT 1 ), thefeature AS ( SELECT f.pname as pname, ...


3

Here is a user contributed PostGIS function called dms2dd. Your data however looks like Degree, decimal Minutes. You may need to modify the function to suite your needs. functions


4

Aha, I was confused for a while, but now I'm not. Generally, I would expect a buffer in UTM to match up pretty well to a geodetic buffer. Close enough you wouldn't be able to see the difference. But, looking at your map, it looks like you're working in a European city, but you're generating your PostGIS buffer in UTM 18, which is valid in the area of New ...


0

One way is re-use this answer this returns start and endpoint for line , use negative offset for other side SELECT ST_StartPoint(ST_OffsetCurve(center_geom, startpointoffset) as start, ST_StartPoint(ST_OffsetCurve(center_geom, endpointoffset) as end, from xx where yyy this makes line SELECT ST_Makeline(l.start, l.end) FROM (SELECT ...


0

Have you set the SRID on the geometries? This code works for me (I have multipolygons, hence the use of ST_Dump): SELECT RandomPointsInPolygon((ST_Dump(ST_SetSRID(geom, 4283))).geom, 50) On my home PC it can do ~20 million points in under an hour based on ~350,000 not too complex polygons.


1

It's probably because you have MultiPolygons in your geometry, which are collections of polygons. The author followed up with a function RandomPointMulti that handled the known issue with MultiPolygons. If you're not sure what geometry type you're working with (Points, Linestring, Polygon, MultiPoints, MultiLinestring, or MultiPolygon), I highly recommend ...


5

To create 1 random point for each polgyon in a table I have used this table and code: DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT id_0 FROM "Grid" LOOP -- RAISE NOTICE 'affected row id: %', r.id_0; UPDATE "Grid" SET "point_geom" = (SELECT RandomPointsInPolygon(geom, 1) FROM "Grid" WHERE "id_0" = r.id_0) WHERE "id_0" = r.id_0; END LOOP; END$$; ...



Top 50 recent answers are included