Tag Info

Hot answers tagged

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

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


3

To add a column to an existing table, use the ALTER TABLE DDL, e.g.: ALTER TABLE my_table ADD COLUMN the_geom_mercator geometry(Geometry,3857); which can be populated from another column (the_geom) using: UPDATE my_table SET the_geom_mercator = ST_Transform(the_geom, 3857) FROM spatial_ref_sys WHERE ST_SRID(the_geom) = srid; (the second line FROM ...


3

You're almost there, you need to declare the sequence as a dependent of the table. ALTER SEQUENCE polygon2_gid_seq OWNED BY polygon2.gid; Now the sequence will cascade drop with the table.


2

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


2

There is ST_LineMerge function http://postgis.net/docs/manual-2.0/ST_LineMerge.html You could try to serve all your rivers network as one MultiLineString ST_LineMerge(ST_Multi(St_Collect(geometry))) The result is also a MultiLineString with segments sewed together. So after ST_LineMerge you could get sewed segments via ST_Dump.


2

Alright, when installing Nominatim a folder 'settings' is created, holding a PHP file named 'settings.php'. Within this file all sorts of constants are defined - also 'CONST_Postgresql_Version'. Simply edit this file and change the value from 9.3 to 9.4 will overcome the initial error message.


2

No space should be (also float8 is the same as double precision and a bit faster to type) UPDATE main_table SET geom = ST_SetSRID( ST_MakePoint(lng::float8, lat::float8) ,4326);


1

You can get the shortest route between two points using PostGIS with ST_Distance (if you want to know the distance) or with ST_MakeLine (for the geometry): http://postgis.net/docs/ST_Distance.html http://postgis.net/docs/ST_MakeLine.html If you want to use the network for routing, then you need a network topology. You could try something like pggraph, ...


1

You can create an unconstrained SRID geometry column to hold the native form and then transform to existing. Here is a contrived example assuming you have polygons that you are copying from a staging table (if you have mixed, you can set type to geometry e.g geometry(Geometry,3857): CREATE TABLE poi(gid serial primary key, geom_native ...


1

shp2pgsql translates a shapefile and its attribute table to sql commands. It does all the heavy lifting for you so the result should be workable as it's being produced, and it's a starting point for you to optimize if you want by: 1) Create spatial index (- I flag in shp2pgsql). You can create a spatial index in an already created table by create index ...


1

I was trying to do some changes and this looks like solution since everything works. I deleted old database and again created new one by creating a spatial database directly in pgAdmin: Created new database (name,owner...) Opened the query dialog box and wrote CREATE EXTENSION postgis; and then SELECT postgis_full_version(); This step ...


1

Create Table p( id serial primary key, geom geometry(point,2100) ); The table has ~250k Points, and with the following query you can find the closest point in the same dataset (that is not the same point). select a.id ,b.id target ,b.d distance from p as a cross join lateral (select distinct on (a.id) b.id ,a.geom <-> b.geom d from p as ...


1

The two area calculation sums are within 0.0638% of each other, which is pretty close. It looks like you are calculating the area of a geodesic polygons, which is a complicated problem, with several different algorithms, each with a slightly different result. I can point out the algorithms used for different versions of PostGIS, but the algorithm for Oracle ...


1

I think you want to use pgr_drivingdistance, as per the documentation. To check what pgRouting functions are available (ie. to see if they installed correctly, you can run this query in the database with PGRouting installed: SELECT p.proname FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = ...


1

Assuming you are using PostGIS 2+, you can make use of the <-> operator. See http://postgis.refractions.net/documentation/manual-2.0/geometry_distance_centroid.html So in your example it might look like SELECT * FROM Stores ORDER BY location <-> 'SRID= 4326;POINT(-82.373978 29.633657)'::geometry limit 3; Note the results may be returned ...


1

@Akhil answered his/her own question but I wanted to post this clearly for posterity in case others find themselves combing through comments to figure this out like I did. TL/DR answer: SELECT tabblock_id AS Block, substring(tabblock_id from 1 for 11) AS Blockgroup, substring(tabblock_id from 1 for 9) AS Tract, substring(tabblock_id from 1 for ...


1

Pros: It's a high performance and flexible key/value that can be indexed and queried. I have a complex schemaless web GIS that uses an HStore column for storing the features properties. Cons: Most WMS/WFS tools like Mapserver, Geoserver and similars can't understand and work with HStore columns, so you end up creating hard coded SQL Views (In Geoserver ...



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