Tag Info

New answers tagged

0

The answer is what @Redoute wrote but change ST_Intersects to ST_Crosses. Because the blue line also intersects with polygon 2 when it touches him...


1

Your expression works on one line compared to one polygon only: The black line touches Polygon 1 and doesn't intersect Polygon 1. In more detail: SELECT x FROM line, polygons WHERE ... means: Build a case from every pair of a line and a polygon, so number of cases is n_lines x n_polygons. Do the WHERE clause for every case and keep cases where it is true. ...


3

Use ST_Snap http://postgis.net/docs/ST_Snap.html as the final step. As input geometries you need your original polygon and extra vertices to be added as MultiPoint. The third term of the function is snapping tolerance. SELECT ST_Snap( ST_GeomFromText('POLYGON (( 7 7, 7 11, 11 11, 11 7, 7 7 ))'), ST_GeomFromtext('MULTIPOINT (( 11 9 ), ( 7 9 ), ( 9 11 ), ( ...


0

Hy Sandra, The hydraulics parameter of [OPTIONS] target is available only if you use the command line of EPANET software. If you try to load the INP file using the the Windows EPANET gui will appear a message like this: Error Report for File *.inp Error 201 at line XXX: [OPTIONS] but it is not a problem to run successfully your EPANET project


1

I think I got it work now. This was pretty easy actually. I had to restart tomcat by using /bin/shutdown.sh and startup.sh instead of service tomcat7 restart. After deploying geonetwork.war from scratch, my geonetwork UI was reset and database was filled with tables, so I guess that's it.


0

Since you are searching for identical points only, the bounding boxes intersect operator "&&" will be most efficient. In case your tables don't have spatial indizes, create them: CREATE INDEX ON r USING gist (geom); CREATE INDEX ON e USING gist (geo); Then for each linestring lookup the matching endpoints and the bigger km value: UPDATE r SET ...


1

More specific to the answer by Redoute is that the two points are projected to EPSG:32619 (WGS 84 / UTM zone 19N), determined by the utility _ST_BestSRID(geog), which have transformed coordinates: ex1: SRID=-32619;POINT(414639.538157217 4428236.06463343) ex2: SRID=-32619;POINT(329274.505728464 4429672.97311587) These points are buffered by 10.0 m (in ...


4

From http://www.postgis.org/docs/ST_Buffer.html: Geography: For geography this is really a thin wrapper around the geometry implementation. It first determines the best SRID that fits the bounding box of the geography object (favoring UTM, Lambert Azimuthal Equal Area (LAEA) north/south pole, and falling back on mercator in worst case scenario) ...


3

Fixed. The problem was is no primary key. In pgAdmin do this request. ALTER TABLE tableName ADD PRIMARY KEY (id); Example for planet_osm_line table and setting osm_id column ,as primary key : ALTER TABLE planet_osm_line ADD PRIMARY KEY (osm_id); untill osm_id is unique.


4

In Postgres column and table names are not case sensitive and everything is converted to lower case before executing the query, see the docs. Therefore, SELECT SomeColumn FROM SomeTable becomes SELECT somecolumn FROM sometable unless you write it as SELECT "SomeColumn" FROM "SomeTable" In my view this is messy, especially when dealing with SQL ...


3

ST_SetSRID part of your query is breaking it. Using set SRID sets the SRID to 32650 without transforming it. So you are setting the SRID to 32650, and then trying to convert it to 32650, but it is already 32650 because you set it as so. So try: select ST_Transform(geom_line , 32650) from my_Table; If this does not work your data may not have a SRID to ...


0

Don't know your data. A template could look like: select st_distance(mall.geom, roads.geom) as dist from roads, mall where roads.name = '16th cross' AND mall.name = 'xyz' order by dist limit 1


1

Hmm it should be able to find it if postgis is in your database search path. Trying doing this: ALTER DATABASE your_db SET search_path=public,postgis; Then connect to your database again and do: CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION postgis_tiger_geocoder;


0

You can try the merge tool wish merge a lot of shp to one


0

You can search for OpenJUMP it's a GIS Java software, you can use it to display PostGIS files or for developing Plugins. You can read more about this GIS Java software here.


3

You can dump the border of polygon with ST_ExteriorRing, then it will find the closest point on the resulting linestring, if you want a point that lies just a little bit outside use ST_Buffer first to add a little bit to the original polygon. WITH polygons AS( SELECT ST_Buffer(ST_GeomFromText('POINT(0 0)', 4326),2) as geom-- this circle will pretend to ...


1

The ERROR: function addGeometrycolumn(unknown, unknown, unknown, unknown,unknown, integer) does not exist means that the postgis extension is not installed on your postgresql. Go here and install it.


1

If you want the intersection, that should be one of the things you SELECT. If you only want rows where there is an intersection, that goes in the WHERE. So you probably wanted (no test, because you didn't provide the full table schema) something like: SELECT id, time, ST_AsText(geom_houses), ST_AsText(ST_Intersection(geom_line, geom_houses)) FROM my_table ...


2

If you're not familiar with projections and the like, you might want to do the NOAA course on this: http://coast.noaa.gov/digitalcoast/training/datums (note that you do need Flash, and its narrated so you need sound; but you can skip the registration if you like, and the ArcGIS specific parts at the end are obviously not needed). Once you have an ...


1

It seems you can use ST_Node to do this faster than ST_Union. I used this query, which I think gives the same result: CREATE TABLE boundaries AS SELECT (ST_Dump(ST_Node(ST_Collect(ST_ExteriorRing(geom))))).geom AS geom FROM circles; What I'm doing is collecting the exterior rings and then noding them, which splits them into individual segments, ending at ...


3

Several ways to do this. One is to find the distance and order ascending. SELECT geom FROM polys, points WHERE points.gid=1 AND ST_DWithin(poly.geom, points.geom, 1000) ORDER BY ST_Distance(a.geom, b.geom) LIMIT 1; I added the ST_DWithin call to show how you might limit the number of candidates (it will make use of the index.)


1

I use ST_ClosestPoint to return the closest point from each polygone, then I calculate the min distance SELECT foo.* from (SELECT min(st_distance(a.geom,ST_ClosestPoint(b.geom,a.geom))) from polyg a,point b) foo


1

You need to add postgis to your search path: Try ALTER DATABASE name_of_your_db SET search_path = public, postgis; Then disconnect from your database and reconnect. It should work.


2

I think this is a primary key issue - does your table have one and is it recognized when you view the table through PGAdmin? I had this same problem, but re-loaded the layer with a primary key field called ID (arbitrary) and all my edit tools are now active on the toolbar. EDIT Using SQL you can add an OID column of 'serial' data type and create a ...


0

Building the union of thousands of circles will be the expensive part. Actually finding the points inside the constructed object will be moderately fast, since the prepared geometry routines will kick in and you'll have an internally indexed query polygon. But building that polygon, could take a while. WITH ( query to build and union circles ) AS circles ...


1

This line is the problem uri.setDataSource("",sql,"long","","lat") The error message you're getting says that the table "select long,lat from ..." doesn't exist. The reason is you're passing the sql as the 2nd parameter; this should be the table name instead. From the API documentation, the parameters should be in this order... ...


0

Assuming the only reason you need these views is for controlling the login column value and who can update what rows, then you don't actually need a view here. Start by creating an INSERT trigger to always set the login column: CREATE OR REPLACE FUNCTION set_login_to_current_user() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN NEW.login = ...


2

Based on the great idea of @radouxju's responce I resolve this issues. Instead of using Reclass function I used MapAlgebra function so the query is : SELECT ST_MapAlgebra(a.rast,b.rast, '(ceil([rast1]/10)*10)+5') AS newrast from my_raster1 a, my_raster2 b I used 'ceil' funtion to get the smallest integer not less than argument see this link And this is ...


0

The source and target parameters in pgr_dijkstra are id's of vertices, not edges. I assume 41 and 50 are edge id's = column gid in table network. Moreover pgrouting is constrained to 32bit-integers as edge- and vertex-id's, so you can't use OSM-id's directly, as they are too big.


4

if you have a constant interval, then you do not need a look up table. You can use the following formula (int(raster@1) / interval) * interval + interval/2 of course, / must be an integer division (rounding to the integer below)


4

You didn't mention qgis nor postgis version, qgis 2.10 doesn't let you edit table without a primary key - which would be the case of your view.


1

This is a comment (too large to be converted) Cliff, I don't think inadequate hardware is the problem. Here's a brief summary of what I've found so far. GDAL's PostGIS raster driver has had past performance issues (see here as well). Although these problems were noted in 2012, I wonder whether GDAL 1.11.2 found in QGIS 2.8 still has this problem. Surely ...


0

Creating a materialized view that represents the street segments with sidewalks can help speed up the query as long as you know the underlying tables won't be updated very frequently. Create the materialized view: CREATE MATERIALIZED VIEW transportation.streets_with_sdw AS SELECT *, CASE WHEN dynamap_id in (select distinct teleatlas_id from ...


2

I guess it's better to answer here than comments on your question: 1) As mentioned source and target are integers that are unique identifiers for starting and ending points of your edge. So if an edge joins with another they share a point in common and you'll see the id repeated. This is a very fundamental piece of routing since travel stops are at the ...


2

You need to cast the string to a geometry then set its SRID to match the geometry column's SRID. select id from database where point_lat_lon = st_setsrid('POINT Z (116.309627 39.991285 148)'::geometry, ST_SRID(point_lat_lon));


0

the pixels that fall within the polygon, and get the sum of the pixel value CREATE TABLE gtur_srtm_inter AS SELECT id, (gv).geom AS the_geom, (gv).val FROM (SELECT id, ST_Intersection(rast, the_geom) AS gv FROM srtm_tiled, gtru_poly_vect WHERE ST_Intersects(rast, the_geom) ) foo; The values ...


0

This is an old question (2 full versions of Ubuntu old; at least 4 'point' versions of QGIS old; several GB of Microsoft CRITICAL-security-hole-updates old). However as with the age-old question of why sheep are found in trees, it is a fair question - and one that in recent weeks has been much on my mind. What the OP seemed to be trying to create is a ...


0

This doesn't explain or fix anything, but you could try using a temp table: create table temp_for_export as select * from table1 where id < '20' Then pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password] [database] temp_for_export and see what that does. Is there any chance your host, user or database is incorrect on the pgsql2shp command ...


2

I am having the exact same issues with rendering rasters in QGIS from PostGIS (see my recent question) I found this post helpful and increasing the following improved raster rendering slightly: shared_buffers = 5000MB work_mem = 100MB maintenance_work_mem = 100MB However, with that said, I totally agree that the performance of PostGIS rasters in QGIS is ...


0

OK so here is the much improved query thanks to the useful pointer given by Jakub WITH POINTS AS ( SELECT point_id, polygon_id, point_geometry as wkb_geometry FROM boundary LEFT JOIN points ), DISTANCES AS ( SELECT polygon_id, point_geometry <-> other_point_geometry as distance FROM ( ...


0

The problem was all about the unneeded symbolic link. sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/pgsql2shp I fixed the issue finally by just removing the symbolic links. sudo rm /usr/local/bin/shp2pgsql sudo rm /usr/local/bin/pgsql2shp sudo rm /usr/local/bin/raster2pgsql Then removed postgis 2.1 with sudo apt-get remove ...


0

Could it be that QGIS is displaying the img pyramids instead of the actual rasters? I had the same problem in ArcGis and in my case the lines were there only when I loaded pyramids, but disappeared when zooming because, in doing so, arcgis switched to the original, full resolution, image.


1

I have resolved this issue. Go through this link and follow all the instructions posted there. Problem Resolved


1

If the lines "go away" when you zoom in, this isn't a data loss problem, it's a rendering problem, so the issue is in QGIS.


3

Well, the absolute difference in planning times is fractions of a millisecond. The native PostgreSQL planner has an advantage because it doesn't actually do anything for a selectivity calculation, it just returns a constant value. The PostGIS selectivity function actually figures out the selectivity, using a test against a spatial histogram of the data. This ...


2

The index in native PostgreSQL is basically the same as that in PostGIS (except for selectivity estimates, where PostGIS is better) so there will be no real performance change on such a simple query. Because you're testing one point/poly combination at a time and not doing any repeats of the same poly in the same query (as would be the case on a spatial ...


3

The purpose of a tile server is to deliver tiles (raster images). If you want to geocode then use/install a geocoder, for example Nominatim. If you want to access raw data then use/install a service offering raw data, for example Overpass API.


2

It's not an elegant solution, but you could try to convert Polygon string representation into WKT and use ST_GeomFromText select ST_GeomFromText( 'POLYGON ' || regexp_replace( '((-74.026142,40.636264),(-74.026142,40.636438),(-74.025818,40.636438),(-74.025818,40.636264),(-74.026142,40.636264))', '\,(?=[0-9])', ' ', 'g') )


1

I have resolved this error: The problem is still your pg_hba.conf file (/etc/postgresql/9.3/main/pg_hba.conf). This line: local all postgres peer Should be local all postgres trust After altering this file, don't forget to restart your PostgreSQL server. If you're ...


2

You have defined meterid as a PRIMARY KEY so they should be unique. You may use Composite Key CREATE TABLE MeterRdg( meterid VARCHAR(5) NOT NULL, reading integer, reader VARCHAR(15), DateRead date, PRIMARY KEY(meterid, DateRead) ) or add a column with counter and use it as a key for whole record CREATE TABLE MeterRdg( ...



Top 50 recent answers are included