Re: [postgis-users] Correct or wrong raster image loading
All right, I got that, thank you.
So is there anyone who uses QGIS plugin to Postgis and managed to display
raster data, because I can't and I don't know what's the problem? Which part of
the database I have to Add in order to display my rasters in QGIS platform?
From: Bborie Park bkp...@ucdavis.edu
To: postgis-users@postgis.refractions.net
Sent: Monday, May 14, 2012 10:45 PM
Subject: Re: [postgis-users] Correct or wrong raster image loading
Giannis,
If your tile size is the same as the raster size, you're only going to
get one tile (and one row). Now if you were specify a tile size smaller
than the raster size (such as 30x31), you would have 117 tiles (and rows).
I wouldn't be concerned about not seeing the whole blob in pgAdmin.
pgAdmin usually does that for data that is particularly large.
-bborie
On 05/14/2012 12:07 PM, Giannis Giakoumidakis wrote:
Thanks for the anwser.
My raster is 390x279, so I use -t 390x279. I get no errors. I didn't
understand from your asnwer, the rows in the table should have been more than
1 that it is now or it is correct like that?
The .sql I get before I use psql is this, in Notepad++:
BEGIN;
CREATE TABLE public.cc00h00m15s (rid serial PRIMARY KEY,rast raster);
INSERT INTO public.cc00h00m15s (rast) VALUES
('010400DDC80B44AA83173FF2C80B44AA8317BF1986D9154421394032D1D677B4AE4140E610860117010400828282828282828282828282...')::raster;
ANALYZE public.cc00h00m15s;
SELECT
AddRasterConstraints('public','cc00h00m15s','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE);
END;
I don't know why in the sql editor of pgAdmin, the: VALUES
('010400DDC80B44AA83173FF2C80B44AA8317BF1986D9154421394032D1D677B4AE41400')
are missing.
From: Bborie Park bkp...@ucdavis.edu
To: postgis-users@postgis.refractions.net
Sent: Monday, May 14, 2012 8:07 PM
Subject: Re: [postgis-users] Correct or wrong raster image loading
What are the dimensions (width/height) of your raster? I just tested
your raster2pgsql command string...
raster2pgsql -s 4236 -I -C -M *.tif -F -t 100x100 testtable
And it has no issues. The generated table will only have 2 or 3 columns
(3 since you specified -F). Each raster column will have one entry in
the raster_columns view.
You may wan to check the SQL generated from raster2pgsql instead of
piping directly to psql. I'm guessing something went wrong in the
raster loading process. Without additional information (error messages,
if any), we really can't say what is wrong.
-bborie
On 05/14/2012 09:01 AM, Giannis Giakoumidakis wrote:
I'm not sure if my loading of raster images is correct.
I use raster2pgsql -s 4236 -I -C -M *.tif -F -t 100x100 public.demelevation
| psql -d gisdb from the manual. What I get is a table that has only 3
columns (rid, rast, filename) and 1 row only (I thought -t would create many
rows). Also, 12 constraints. Finally, 1 row in the raster_columns view,
which contains all the details of the loaded raster.
The difference with a loaded shapefile is that this table has 55.000 rows
that contains all the vector data details.
So I have a major problem when I connect to the database through QGIS. There
I can load and display normally a shapefile from the database (it appeas
with MULTIPOLYGON type), but all the tables which contains rasters appear
with no geometry type and can't be displayed in QGIS.
Can anyone tell me where is my fault, in the QGIS or in the first loading of
the raster images in the database? Thanks.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Closing polylines
Sorry Nicolas, the attachment is in a normal dump format, not shapefile format ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Uploading a folder with many files
I agree we should have an option to load all the rasters in different tables
mostly if you tile them. However this little function should help you split
your table into many:
--
-- SplitTable
-- Split a table into a series of table which names are composed of the
concatenation of a prefix
-- and the value of a column.
-- sourcetablename - The name of the table to split into multiple table
-- targettableschema - The schema in which to create the new set of table
-- targettableprefix - The prefix of the set of table names to create.
-- suffixcolumnname - The name of the column providing the suffix to each name.
--
-- Example to split the table 'test' into a set of table starting with 't_' and
-- ending with the value of the column 'rid' to be created in the 'public'
schema.
--
-- SELECT SplitTable('test', 'public', 't_', 'rid');;
--
CREATE OR REPLACE FUNCTION SplitTable(sourcetablename text, targettableschema
text, targettableprefix text, suffixcolumnname text)
RETURNS int AS
$BODY$
DECLARE
newtablename text;
uniqueid RECORD;
BEGIN
FOR uniqueid IN EXECUTE 'SELECT DISTINCT ' || quote_ident(suffixcolumnname)
|| ' AS xyz123 FROM ' || sourcetablename LOOP
newtablename := targettableschema || '.' || targettableprefix ||
uniqueid.xyz123;
EXECUTE 'CREATE TABLE ' || quote_ident(newtablename) || ' AS SELECT * FROM
' || sourcetablename || ' WHERE ' || suffixcolumnname || ' = ' ||
uniqueid.xyz123;
END LOOP;
RETURN 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT;
Pierre
-Original Message-
From: Giannis Giakoumidakis [mailto:ggiakoumida...@yahoo.com]
Sent: Monday, May 14, 2012 10:27 AM
To: Pierre Racine; PostGIS Users Discussion
Subject: Re: [postgis-users] Uploading a folder with many files
This works but it loads all different raster data in one table, so in the
raster
columns view it appears only one row about these layers. I dont think this is
what I want, because my aim is to load each raster layer in QGIS. Is it
possible
somehow to load many raster layers at once, everyone in a seperate table??
Thanks.
From: Pierre Racine pierre.rac...@sbf.ulaval.ca
To: Giannis Giakoumidakis ggiakoumida...@yahoo.com; PostGIS Users
Discussion postgis-users@postgis.refractions.net
Sent: Thursday, May 10, 2012 5:10 PM
Subject: RE: [postgis-users] Uploading a folder with many files
The PostGIS raster loader, raster2pgsql, supports wildcard so you can do:
raster2pgsql -I -C -F c:/temp/*.tif schema.table| psql -U postgres -d batabase
Pierre
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of Giannis Giakoumidakis
Sent: Thursday, May 10, 2012 5:19 AM
To: Post GIS Users Discussion
Subject: [postgis-users] Uploading a folder with many files
I want to upload to a database a big number of raster files (.tif), for
example
500, in a folder. They are numbered in a row, for example c001, c002,...,
cc500.
Is there any way to upload all with one command or the whole folder at once,
or
I have to do this one by one? Thanks.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] z,m,zm geometries
Hi there. I'm trying to convert from esri's st_ geometry type to postgis' one. I can't convert to wkt using esri's functions and build a geometry in postgis using geomfromtext. Problems start when there are z, m, or zm coordinates. Mind that I'm on postgis 1.4 since that's the latest supported by esri. Now, when I convert from esri using their function I get a text like: LINESTRING Z ... When I pass it to geomfromtext, or even st_geomfromewkt, postgis does not like this... I posted on esri's forum and got a slap in the hand saying postgis is not following the ogc standard... I've done some reading and all I could find are references to POINTZ, POLYGONZ, etc. (suffixes withouth spaces) in v.1.2.0 of the standard. But this does not seem to be the spec for encoding wkt, just the geometry type to be put into geometry_columns. Either way, I could not get postgis 1.4 to accept pointz or any other geometryz type. I found references to postgis 2.0 doing this... but that is beyong my grasp for now. So my 2 questions are: - is there anyway i can get this to work in postgis 1.4 (main question)? - and as long as we're in the subject, how is this related to the ogc sfs spec? are they right? Thanks for your time! Regards, Duarte ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] PostGIS KNN best practices
Hi All, Pretty excited by the new operators - and #, but a bit confused as to how to use them in a query. The two examples from P. Ramsey back in late 2011 ( http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postg is/ ) included doing a KNN on a single point to a cloud of points, i.e. SELECT name, gid FROM geonames ORDER BY geom - st_setsrid(st_makepoint(-90,40),4326) LIMIT 10; or doing KNN on non-point different geometries, where the first neighbor by - or # might not be truly the first i.e. with index_query as ( select st_distance(geom, 'SRID=3005;POINT(1011102 450541)') as distance, parcel_id, address from parcels order by geom # 'SRID=3005;POINT(1011102 450541)' limit 100 ) select * from index_query order by distance limit 10; So, how would one grab the first nearest neighbor for all points in a dataset? This is how I used to do it: CREATE TABLE n2180_560_height AS SELECT x, y, height FROM (SELECT DISTINCT ON(veg.gid) veg.gid as gid, ground.gid as gid_ground, veg.x as x, veg.y as y, ground.z as z, veg.z - ground.z as height, veg.the_geom as geometry, veg.class as class FROM (SELECT * FROM n2180_560 WHERE class = 5) As veg, (SELECT * FROM n2180_560 WHERE class = 2) As ground WHERE veg.class = 5 AND veg.gid ground.gid AND ST_DWithin(veg.the_geom, ground.the_geom, 10) ORDER BY veg.gid, ST_Distance(veg.the_geom,ground.the_geom)) AS vegpoints; ST_DWithin prevents a full cross join, but is a sloppy way to do this, as it requires a priori knowledge of the end cases. I'm hoping there's a subquery or some such magic that would allow me to use the distance operator to a similar end. . Thanks, Best, Steve http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather Geographic Information Systems (GIS) Manager (216) 635-3243 s...@clevelandmetroparks.com http://www.clemetparks.com/ clevelandmetroparks.com image001.png___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Closing polylines
You can work around this problem by using the Postgis topology capability. It will build polygons (faces) from such lines which you can use as topopolygons or extract as conventional geometries. --- On Tue, 5/15/12, george wash gws...@hotmail.com wrote: From: george wash gws...@hotmail.com Subject: Re: [postgis-users] Closing polylines To: postgis-users@postgis.refractions.net Date: Tuesday, May 15, 2012, 7:30 PM Thank you Nicolas, I understand the second point (a single segment poly) but I am having a different sort of problem from your first point. I haven't been able to pinpoint it yet but I suspect it happens when the segments are touching, e.g. the endpoint of one is same as the startpoint of another one (but the segments are not necessarily in an orderly sequence). I also suspect that the problem is caused by the start and end points being at opposite ends of the segments eg 110, 20.10,10.1 so that the closest segment is not necessarily the next one in the sequence. This is more difficult to explain than to show, so I have attached the seg table (in shapefile format) containing a very small subset of the data I am using to test your functions, just in case you have the time to look at it. Thanks again for your help -Inline Attachment Follows- ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] One-click installer or building from the source code (postgresql)
G'day Muni, A good rule for getting an answer on a mailing list is to ask a clear question - if you don't get a response after three weeks you might want to consider rephrasing it, or being more specific. You have provided very little information on what you are actually trying to achieve, or on what sort of system, what constraints you are working under or what scale and type of data you are managing. For a start, typing postgresql arcsde into google returned at least 60 000 records, the first ten of which included an ESRI supplied installation guide and a number of blogs and email lists discussing this combination, I would suggest you try this route, and then bring in some specific questions. good luck Ben On 15/05/2012, at 2:14 AM, Melpati, Muni wrote: Can someone atleast provide references to previous studies with regards to using a combination of postgresql, PostGIS and ArcSDE products for maintaining GIS and raster data. Thanks in advance. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] z,m,zm geometries
On Tue, May 15, 2012 at 3:02 PM, Duarte Carreira dncarre...@gmail.com wrote: Hi there. I'm trying to convert from esri's st_ geometry type to postgis' one. I can't convert to wkt using esri's functions and build a geometry in postgis using geomfromtext. Problems start when there are z, m, or zm coordinates. Mind that I'm on postgis 1.4 since that's the latest supported by esri. Now, when I convert from esri using their function I get a text like: LINESTRING Z ... When I pass it to geomfromtext, or even st_geomfromewkt, postgis does not like this... I posted on esri's forum and got a slap in the hand saying postgis is not following the ogc standard... I've done some reading and all I could find are references to POINTZ, POLYGONZ, etc. (suffixes withouth spaces) in v.1.2.0 of the standard. But this does not seem to be the spec for encoding wkt, just the geometry type to be put into geometry_columns. Either way, I could not get postgis 1.4 to accept pointz or any other geometryz type. I found references to postgis 2.0 doing this... but that is beyong my grasp for now. So my 2 questions are: - is there anyway i can get this to work in postgis 1.4 (main question)? Are you wedded to the Z? Do you have a way to strip the Z from the ESRI geometry before outputting the text? Perhaps you could try WKB instead? (Still might have problems with type numbers, but maybe worth a try?) - and as long as we're in the subject, how is this related to the ogc sfs spec? are they right? The 1.0 and 1.1 specs made no mention of higher dimensions (and hence provided no guidance on what form the WKT should take). Not sure if 1.2 did, but I don't think so. ISO SQL/MM does talk about them, and that's the pattern ESRI is following. PostGIS 2.0 consumes that kind of text, but that's no solace to you. P. Thanks for your time! Regards, Duarte ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] z,m,zm geometries
On Tue, May 15, 2012 at 3:02 PM, Duarte Carreira dncarre...@gmail.com wrote: - is there anyway i can get this to work in postgis 1.4 (main question)? Oh, you're getting text and PostgreSQL has some quite powerful regex functions. You can probably strip the Z from the text before passing it along to PostGIS. P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
