Tag Info

New answers tagged

2

I take it that there are about 1200 roads in your vias table. The problem is that you are assuming that ST_ClosestPoint is an aggregate function, but it is not. It is called on each row in your table. If that is not what you want, you need to get the closest road first. There are other examples of that on this site, but it'll probably be a combination of ...


1

Thanks to @RhodiumToad on #postgres, trick is to use sub.geom instead of table. select row_to_json(t) from ( select * from ( select * from table where height>1500 ) as sub where st_intersects( st_transform( st_setsrid( ST_geomfromgeojson( ...


0

The Issue was resolved with a fresh installation of OSGeo


2

It is never necessary to use Create Spatial Type on a properly configured enterprise geodatabase. Create Spatial Type is a limited subset of Enable Enterprise Geodatabase, where only the SDE.ST_GEOMETRY datatype is wanted. Quoting the documentation (emphasis mine): This allows you to use the ST_Geometry SQL type to store geometries in a database that ...


1

GeoServer supports neither, there is a discussion ongoing on how to implement support for HSTORE here: http://osgeo-org.1560.x6.nabble.com/Re-Geoserver-Question-about-PostgreSQL-and-JSON-or-HStore-td5218145.html Supporting JSON columns would be quite a bit more complicated.


0

Using pg_dump and pg_restore is a recommended method when you have access to a powerful development machine, a less powerful production server, and sufficient bandwidth between them. I used a modified version of this method to create a simplified database dump for stylesheet development on less powerful machines. This method brings a few advantages It's ...


0

select (ST_Azimuth(h.vec) - ST_Azimuth(h.seg)) from ( select ST_MakeLine(cp.p, point.geom) vec, ST_MakeLine(cp.p, ST_LineInterpolatePoint( line.geom, ST_LineLocatePoint(line.geom, cp.p) * 1.01) ) seg from ( select ST_ClosestPoint(point.geom, ...


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


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


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


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

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


0

In my case, the answer was really simple: I needed to make the create extension and create table before the set seach_path to inserts This will not work: SET search_path = myschema, pg_catalog; CREATE EXTENSION IF NOT EXISTS postgis; CREATE EXTENSION IF NOT EXISTS postgis_topology; CREATE EXTENSION IF NOT EXISTS adminpack; CREATE TABLE IF NOT EXISTS ...


1

A self-join allows you to operate on the relationship between pairs of two features. But I don't think you're interested in pairs: for each feature, you want to operate on the relationship between that feature and all other features in your dataset. You can accomplish this with a subquery expression: CREATE TABLE parcels_trimmed AS SELECT id, ...


0

You are missing a comma in line 61 of your code: var LayerActions = { all: function(){ sublayers[0].setSQL("SELECT * FROM completed_072315"); return true; }, small: function(){ sublayers[0].setSQL("SELECT * FROM completed_072315 WHERE voltage_st = '69'"); return true; }, ...


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


0

The other thing to watch is this: are the two shapes strictly identical, i.e with the exact same number of decimals ? When you query a geometry in Oracle with sqlplus, you will only see up to 10 characters (including sign and decimal separator), so you may not see all decimals. If you then do something like cut and paste the output to insert it into ...


0

OrbisGIS 5.0 is an open source GUI tool specialized at database view/processing. The built-in SQL console editor have syntax highlighting, code completion and sql functions search engine. In the geocatalog using drag&drop you can transfer shape files to the database. The table editor has been specifically designed for viewing/editing large database ...


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


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


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

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


0

If I understand correctly you should be able to do a table join then simply assign a color in your polygon styling. function drawPolygon(feature, olmap, hexFillColor, opacity) { var selected_polygon_style = { strokeWidth: 1, fillColor: hexFillColor, fillOpacity: opacity, } feature.style = selected_polygon_style; ...


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


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.


0

Here is what I ended up doing: WITH index_query AS ( SELECT ST_distance_sphere(location, 'SRID=4326;POINT(-82.373978 29.633657)'::geometry) as distance, ST_X("Stores"."location"), ST_Y("Stores"."location") FROM "Stores" ORDER BY location <-> 'SRID=4326;POINT(-82.373978 29.633657)'::geometry LIMIT 100) SELECT * FROM index_query ORDER ...


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


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


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


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


0

Thank you @vince and @Ben S Nadler for the heads up! I added those fields manually and then created the relationship classes.


0

Postgres works fairly well as an ArcSDE GDB, but as the number of datasets increases, so does the connection time. This is not an issue with other enterprise databases. For example, we have an Oracle SDE instance with 5000 datasets which takes about 5s to connect. We also have a postgres SDE instance containing the same data and it takes about a minute. ...



Top 50 recent answers are included