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


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

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

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

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

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

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

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


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

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


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


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 To easily re-load your layer with a primary key that will be recognized by ...


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

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

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

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


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


1

It also really depends how complex your geometries are. If you have large geometries with a lot of points, it would take much longer to process than smaller ones. Do a MAX(ST_NPoints(geom)) on your tables to see how bad they are. In that case you could gain some by adding an ST_Simplyfy step SELECT r.geom FROM public.my_table r WHERE EXISTS ( SELECT gid ...



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