Tag Info

Hot answers tagged

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


3

Reza, 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. Which OS are you on? PostGIS windows 2.2 experimental binaries have SFCGAL and can be downloaded here: ...


3

Look at creating a role (and possibly a user in that role) that only has SELECT privileges on a table, schema or group of tables. See the Postgres docs on creating roles. Once you have created a role, look at granting that role particular permissions. Available permissions are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, and so you want ...


3

ST_StartPoint is the correct function to find single nodes at the start of a Linestring, however it does not work with MultiLinestring, so you will need to use ST_Dump to get the constituent Linestrings. If I have understood you question correctly, you then want all start points which are not also end points for more than one line, ie, points where two ...


3

For example, if you have a row in your table which you want to select as the center of the radius, and the numerical column in which you're storing the victims is called deaths: WITH subquery AS (SELECT the_geom FROM tablename WHERE name = 'this is my center') SELECT sum(tablename.deaths) FROM tablename, subquery WHERE ST_Intersects(tablename.the_geom, ...


2

Note that planners have difficulty with subqueries, and your example can be rewritten without subqueries. A flattened query should look like this: SELECT A.* FROM osm_addr2 AS addr, osm_addr2 AS POI WHERE POI.osm_id=-332537 AND ST_Intersects(addr.geometry, POI.geometry); There's a relevant example in the manual (last two SQL examples), where a subquery is ...


2

I can't leave a comment due to low reputation, so will extend @pistachionut's answer as a separate one. In my case the contents of /usr/local/pgsql/data are owned by user _postgres. Thus, to recreate missing directories it is necessary to run the commands as that user: sudo -u _postgres mkdir -p /usr/local/pgsql/data/{pg_tblspc,pg_twophase,pg_stat_tmp}/ ...


2

As has been suggested earlier and in the ST_Split documentation, you must first snap your line to the points and then call ST_Split. If you are like me, an example is worth more than words: DROP TABLE IF EXISTS split; CREATE TABLE split AS( SELECT (ST_Dump(ST_Split(ST_Snap(a.the_geom, b.the_geom, 0.00001),b.the_geom))).geom FROM line_table a JOIN ...


2

Sure, using ST_Contains SELECT lines.* FROM lines, polygons WHERE ST_Contains(polygons.geom, lines.geom)


2

In QGIS 2.6.1, go to menu Database->DB Manager->DB Manager, set the database connection and you will see the raster table. You can drag and drop it or right click and select Add to canvas. From your description of the problem, you are using the regular "Add PostGIS Layers", which will not show your raster table, since it is for vectors and ...


2

Try this SELECT SUM(length) As length FROM (SELECT DISTINCT ON (r.gid) r.gid, ST_Length(r.geom) As length FROM roads AS r JOIN cadastre AS c ON ST_Intersects(r.geom,c.geom) WHERE ST_Length(r.geom)>500 AND ST_Area(c.geom)>2000 ORDER BY r.gid ) As rc; ...


2

Try something like this, sorry had to replace your variables with real values so I could test, but should be easy enough for you to replace back with what you had. Also note I replace your ST_MakeBox2D/ST_SetSRID with ST_MakeEnvelope which is shorter and generally more accurate. WITH c AS (SELECT -88.505 As topLon, 41.8046 As topLat, -88.405 As botLon, ...


1

You should probably look to a database as soon as your mosaic is non-trivial. As user30184 pointed out, there are examples of that. However Image Mosaic has different levels of capability in different versions of GeoServer. The documentation page you linked to appears a bit outdated on the supported indexes (even on the 2.6 version of that page), but there ...


1

As Mike noted, your query should work fine. are you having issue with it or is your question if you have a table of locations how to get the min, max, avg for the table points of interest (POI). Assuming that is your question your query would look something like SELECT poi.gid, poi.geom, max(price), min(price), avg(price) FROM price_table INNER JOIN ...


1

Yes, that should work (have you tried it?) PostgreSQL (the database software behind PostGIS) supports many aggregate functions, which can also be modified to window functions (if you need that). PostGIS has other aggregate functions, such as ST_Collect, ST_Extent and more, if you need them.


1

I was able to force PostgreSQL to use the data folder F:\db\ by creating the following batch file (and manually turning PostgreSQL on/off): start "" /min "C:\<PostregreSQL>\bin\pg_ctl.exe" start -D "F:\db" -w I found this solution here: http://www.garretwilson.com/blog/2014/03/09/postgresql-management-windows.xhtml


1

From what you described, you don't need any extra common keys, as the geometries will take that role. So just create your query and join the tables spatially. Eg. something like this with corrected names and fields you want to select: SELECT congdist.name, congdist.representative, territories.info FROM congdist, territories WHERE ...


1

Apperantly, from your question I think you need to have some basic concept on database management(esp spatial database) and GIS softwares(QGIS) too.Both QGIS and PostgreSQL have comprehensive documentation you really need to consult with. To add @ John Barça, You can not connect postgresql to qgis rather reverse is done to solve the problem. If you need to ...


1

PostgreSQL doesn't use indexes for functions, it uses indexes for operators only. What happens is function inlining. ST_INTERSECTS is defined as: CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry) RETURNS boolean AS 'SELECT $1 && $2 AND _ST_Intersects($1,$2)' LANGUAGE 'sql' IMMUTABLE; And so the query gets rewritten to use ...


1

I have found that rearranging the query so that the sub-query is at the same level as the initial select, essentially a Cartesian product, but then using the where clause to restrict the records read, will cause the indexes to be used and avoid a full table scan. SELECT * FROM osm_addr2 AS addr, (SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) ...


1

I had a similar problem an I solved it with the following statement: SELECT city.name, postcode.tags->'postal_code' FROM planet_osm_polygon postcode JOIN planet_osm_polygon city ON ST_INTERSECTS(city.geometry, postcode.geometry) AND NOT ST_Touches(city.geometry, postcode.geometry) WHERE a.name = 'cityname' AND b.boundary = 'postal_code'; ...


1

It looks fine and the distance is always measured in the units the SRID used defines. For 4326 that would be in angular units. Unless you use geographies like you found out, that is. Read more in the official docs here. Why 100 km is needed for Chicago is up to you and your data. It doesn't sound that much for a huge city and in fact if I check Wikipedia, ...


1

You'll need to translate the UTM zone to an EPSG code, use ST_FromText to create a geometry object in that projection, and then use ST_Transform to transform your geometry object into whatever SRID you are using for the entire table. I haven't tried it, but this link gives a description of how to programatically convert from latitude/longitude to UTM zone; ...


1

I think you should be able to use this function (from here): -- Function: utmzone(geometry) -- DROP FUNCTION utmzone(geometry); -- Usage: SELECT ST_Transform(the_geom, utmzone(ST_Centroid(the_geom)) ) FROM sometable; CREATE OR REPLACE FUNCTION utmzone(geometry) RETURNS integer AS $BODY$ DECLARE geomgeog geometry; zone int; pref ...


1

Use spatial join between your points and your lines, with the JOIN_ONE_TO_ONE option. You can get attributes from both points using the "first" and "last" merge rules. If the order is important, generate each end point type (START /END) separately using feature vertices to points then join.


1

Looks like you found a good solution. If in the future you don't want to, or can't, rely on an external shell script you can bring your ruby implementation into the database through PL/Ruby.



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