Tag Info

Hot answers tagged

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.


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


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

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


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


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.


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.


3

you should refer to every fields as NEW.field_name ex: WHEN degree(ST_Azimuth(NEW.geom, ST_Point(NEW.x_cord, NEW.y_cord))) < 22.5 THEN 'Center'


3

the problem is this line... srcFile = os.path.join("DISTAL-data", "TM_WORLD_BORDERS-0.3","C:\Users\n\Downloads\TM_WORLD_BORDERS-0.3\TM_WORLD_BORDE‌​RS-0.3.shp") os.path.join() will create this file path (under windows) \DISTAL-data\TM_WORLD_BORDERS-0.3\C:\Users\n\Downloads\TM_WORLD_BORDERS-0.3\TM_WORLD_BORDERS-0.3.shp which probably isn't a valid path. ...


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


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


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


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


2

Since you already know the routes you want to illustrate, I'm not sure pgRouting is the right tool for the job—I believe it intended to support questions involving routing where you have a number of alternate path options, and you want to identify the most optimal route, chiefly among driving scenarios. For example a shorter, yet "higher cost" route (a main ...


2

It depends a lot on your input geometries. You're running what looks like a conflation query here, and hopefully that means that your geometries in both sets are of fairly similar sizes and not overly complex. That means that any given geometry on one side will be tested against a fairly limited number of geometries on the other (in a parcel-against-parcel ...


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


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


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


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


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


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

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


2

If you only want Polygons or Multipolygons from ST_MakeValid you can use ST_Dump to extract the constituent geometries and then test for the geometry type. ST_MakeValid will sometimes produce Points or LineStrings which is where the GeometryCollection is coming from. Try something like: SELECT g.geom, row_number() over() AS gid, FROM (SELECT ...


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


2

You need to make sure your set search_path call always includes the schema postgis is installed in. So instead of doing: SET search_path=vmplan201506; Make sure you are doing SET search_path=vmplan201506,public,tiger; If you have other tables in public you don't want seen, then move postgis into another schema and make sure that schema is always ...


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


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.


1

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


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



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