Re: [postgis-users] Unprojected lat/long to 3081?
EPSG 3081 is a metre projection, so you need to transform your points from lat long into the appropriate projection. Try something like this: *UPDATE event_table* *SET the_geom = ST_Transform(ST_SetSRID(ST_Point(event_location[1], event_location[0]), 4326), 3081) * This assumes your input latlons use the WGS84 datum. Cheers, Francis On 4 February 2011 09:33, Aren Cambre a...@arencambre.com wrote: I have a database containing a Postgres native point data type *event*that has the latitude and longitude for certain events. An example value of *event* is *(32.7748777996749,-96.7680574022233)*. Note: this is *not* a PostGIS data type. It's a native Postgres type. I tried to convert this into a PostGIS gemoetry field *the_geom* and project into 3081 at the same time using this: *UPDATE event_table* *SET the_geom = ST_SetSRID(ST_Point(event_location[1], event_location[0]), 3081)* (This derived from the example at http://postgis.refractions.net/docs/ST_Point.html and reinforced by some Google searching.) The problem is when I import this layer into a QGIS project with several 3081-projected layers, all of my event points are ending up in the Pacific Ocean just off Mexico's west coast. 3081 is a projection for Texas, and that's where all my other data is. Am I doing something wrong with the conversion? I assumed that there really is not a projection *per se* because the input is pure lat/long? Aren ___ 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] suitable projection in metric system ?
Hi Ahmet, My quick Googling hasn't come up with a widely used pre-defined projection for Turkey that is non zonal (either UTM or Gauss-Kruger), at least not in the EPSG database. Apparently, MapInfo ships with a Turkish Lambert projection in its mapinfow.prjhttp://testdrive.mapinfo.com/techsupp/miprod.nsf/8248bf2b72f6949585257125006b035a/215efdb0c93153be85256a410055416a?OpenDocumentfile, which is widely available on the web. I took the parameters from that projection and created an entry on spatialreference.orghttp://spatialreference.org/ref/sr-org/7006/as SR-ORG:7006. This can be inserted into PostGIS as a new SRS as: INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 97006, 'sr-org', 7006, '', 'PROJCS[Turkish Lambert Conformal (K.Eren - ED50),GEOGCS[GCS_European_1950,DATUM[D_European_1950,SPHEROID[International_1924,6378388.0,297.0]],PRIMEM[Greenwich,0.0],UNIT[Degree,0.0174532925199433]],PROJECTION[Lambert_Conformal_Conic],PARAMETER[False_Easting,1003827.0],PARAMETER[False_Northing,-1183453.0],PARAMETER[Central_Meridian,36.0],PARAMETER[Standard_Parallel_1,37.5],PARAMETER[Standard_Parallel_2,40.5],PARAMETER[Scale_Factor,1.0],PARAMETER[Latitude_Of_Origin,25.0],UNIT[Meter,1.0]]'); This can then be used as SRS number 97006. Note that this projection uses the ED50 datum. If your coordinates are in WGS84 (the standard for GPS) or some other datum, it might be convenient to create a new SRS using the above parameters but based on another datum. Furthermore, If you are happy to keep your data in geographic coordinates (lat/lon) you may wish to investigate the Geography type as Emily suggested. Be aware, however, that this has some cost in terms of speed for some operations, and not all operations defined in PostGIS for Geometry are currently available for Geography types. Good luck, Francis Markham 2010/11/5 ahmet temiz ahmettemi...@gmail.com hello I was wondering if you could tell me which projection in metric system is suitable for whole country (TURKEY) ? I thought to use UTM, but it has three different zones. If you answer me, I will appreciate regards -- Ahmet Temiz Jeoloji Müh. Planlama ve Zarar Azaltma Dairesi Başkanlığı Bilgi ve CBS grubu Eskişehir Yolu 10. km. Lodumlu / Ankara Tel : 0 312 2872680 / 1535 ___ 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] pgsql2shp dbf file encoding
0x57h is the dreaded Windows-1252 codepage. I believe new versions of shapelib allow this to be set when the shapefile is created. Cheers, Francis Markham On 25 October 2010 20:05, Mark Cave-Ayland mark.cave-ayl...@siriusit.co.uk wrote: Denis Rykov wrote: Try to export postgis data to shapefiles with pgsql2shp (pgsql2shp-core.h 5870 2010-08-28 09:16:32Z mcayland) If open *.dbf file I see the value in my dbf files at byte 29 is 0x57h. Is the 0x57h value is default? Why not 0x00h? With 0x57h encoding my shapefiles looks not correct in any GIS software. I don't think it's currently set to anything, so I guess this would be the default? Perhaps we should provide a mapping from PostgreSQL database encoding names to shapefile encoding values in a table somewhere? Anyone know which encoding 0x57h represents? ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ 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] how to write the sql if i want to find a geometry's d-distance neighbors?
Hi Sunpeng, Definitely use ST_DWithin. -Francis On 13 September 2010 06:51, sunpeng blueva...@gmail.com wrote: Hi, I've the following table,and has created the index on uid and location; CREATE TABLE points ( location geometry, //assume it's a 2-dimension point uid integer ) Now, I've a point P with uid= 12345, and want to find the neighbors which are within 20, how to write the sql?Is this right? select st_astext(location) from points a, points b where b.rid = 12345 and ST_DWithin(a.the_geom, b.the_geom, 20) or use st_buffer? which one is more efficient ? 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
Re: [postgis-users] making polygons from center + vertex
Sure is, check out the ST_Buffer function http://postgis.refractions.net/docs/ST_Buffer.html ST_Buffer(geometry g1, float radius_of_buffer); If you give it a point as g1, and the radius of the circle it will calculate a pseduo-circular polygon for you. If you have two points g1 and g2 you could use ST_Buffer(g1, ST_Distance(g1, g2)); -Francis On 12 August 2010 11:36, Arnaud Sahuguet arnaud.sahug...@gmail.com wrote: I found this really neat Google Maps example where you can draw polygons using 2 points: the center and one vertex. See http://www.barnabu.co.uk/geapi/polyplot/ Is there a POST GIS function to create the corresponding polygon? regards, -- Arnaud Sahuguet ___ 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
[postgis-users] ST_Azimuth on the spheroid
Is there, or are we likely to see a way of calculating ST_Azimuth on the spheroid? Is an implementation, overloading ST_Azimuth to accept the GEOGRAPHY type to be expected? I suspect the code has already been written, given the existence of ST_Distance_Spheroid which presumably uses Vincenty's Inverse formula (which also calculates azimuth). Cheers, Francis Markham ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_Distance_Spheroid SRID
Hi all, The documentation for ST_Distance_Spheroid ( http://postgis.refractions.net/docs/ST_Distance_Spheroid.html ) states that it does not look at the SRID of a geometry and will always assume its in WGS 80 long lat. What is WGS 80? Is it WGS-84, GRS-80, or something else entirely? Cheers, Francis ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Get the bounding box?
You might want to look into the GEOGRAPHY type http://postgis.refractions.net/docs/ch04.html#PostGIS_Geography to do this nicely. -Francis On 21 July 2010 12:57, Michael A. Peters mpet...@shastaherps.org wrote: Thank you. It did work with one caveat, I assume because all my shape data is in WGS84 it added an equal number of specified decimal degrees to each side but that was cake to adjust post ST_Expand and it now does exactly what I want it to do. Hallo ST_Expand should do the trick. http://postgis.org/documentation/manual-1.5/ST_Expand.html /Nicklas 2010-07-20 Michael A. Peters wrote: *snip* I can't figure out how to get the bounding box for the intersection. It would be nice to be able to simply ask for the bounding box + X kilometers in each direction if PostGIS can do that (I'm running 1.3.2 on CentOS - that's stock EPEL build) but I can do the math outside of PostGIS if necessary. I just can't figure out how to get the bounding box returned. - Michael A. Peters http://www.shastaherps.org/ ___ 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] Dot Density idea
Looks great Martin. Another way of generating non-clumpy random dots without a grid-like pattern is to use a repulsive function. See for example, http://www.statisticsblog.com/2010/06/repulsive-dots-pattern-the-difference-of-distance/ Cheers, Francis On 7 May 2010 08:11, Martin Davis mbda...@refractions.net wrote: Check out this blog post for some images of different kinds of random point fields: http://lin-ear-th-inking.blogspot.com/2010/05/more-random-points-in-jts.html Martin Davis wrote: Sounds like it could work - with maybe a bit of fiddling to deal with cases where the grid cells overlapped the polygon only slightly? Random perturbation by cell radius can still result in some points being very close together. (And I think this would also be an issue where only a small part of each grid cell overlapped the polygon). This may or may not be desirable. Perhaps a further check could be made to reduce the radius for points where this occurs. Or maybe some sort of simulated annealing process could be use to push the points into a more even distribution. M Paul Ramsey wrote: Even-yet-random :) nice requirement. How about just starting with a regular grid and then perturbing the elements randomly with a radius of a cell size? You can use the area of the polygon and number of needed points to calculate the appropriate cell size and go from there. P On Thu, May 6, 2010 at 10:28 AM, Martin Davis mbda...@refractions.net wrote: Good point about the need for even distribution of the points. That seems like a whole lot harder to code than simply randomly placing points in a polygon. Does anyone have any pointers to algorithms for producing this effect? George Silva wrote: The really big problem with dot density is that dots can overlap themselves, masking the real number, so if anything will be developed in this area, the points should be A) evenly distributed or B) randomly distributed, but with some sort of colision tests, so there is no or little overlap. This is a interesting idea, especially if we could make a materialized view with those points, which could be added to GIS software for presentation. George On Thu, May 6, 2010 at 1:53 PM, Sufficool, Stanley ssuffic...@rov.sbcounty.gov wrote: Looks nasty, but it might work: select st_line_interpolate_point( st_intersection( the_geom, st_makeline( st_pointn(st_exteriorring(the_geom), (rand1.rand * st_npoints(st_exteriorring(the_geom)))::int), st_pointn(st_exteriorring(the_geom), (rand2.rand * st_npoints(st_exteriorring(the_geom)))::int) ) ) ,rand3.rand ) from insert_your_table_name_here, (select random() as rand, generate_series(1,1000) as point_number) as rand1 JOIN (select random() as rand, generate_series(1,1000) as point_number) as rand2 ON rand1.point_number = rand2.point_number JOIN (select random() as rand, generate_series(1,1000) as point_number) as rand3 ON rand2.point_number = rand3.point_number WHERE st_geometrytype( st_intersection( the_geom, st_makeline( st_pointn(st_exteriorring(the_geom), (rand1.rand * st_npoints(st_exteriorring(the_geom)))::int), st_pointn(st_exteriorring(the_geom), (rand2.rand * st_npoints(st_exteriorring(the_geom)))::int) ) ) ) = 'ST_LineString' AND oid = 5030 /* Enter your own OID here */ limit 100 -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Martin Davis Sent: Thursday, May 06, 2010 8:56 AM To: John Abraham; postgis-users@postgis.refractions.net; Martin Davis Subject: Re: [postgis-users] Dot Density idea I was thinking the same thing! strk wrote: ST_RandomPoinsOnSurface(geometry, numpoints) would be an interesting function indeed. Sounds like a good job for GEOS/JTS. --strk; On Mon, May 03, 2010 at 10:49:32PM -0600, John Abraham wrote: One of the things I miss about using ESRI's GIS is the ability to do dot-density maps. Within a polygon, the number of dots is proportional to a value, and the dots are randomly placed. I find it useful to be able to present several data values at once (e.g. blue dots for population, red dots for employment). I also find that it is a more intuitive way of scaling for zone size than dividing the value by the area of the zone. That is, the count of the dots represents the actual number, but the density of the dots represents the density of the number. So I don't have to decide whether to divide the value by the area of the polygon to plot density: both the absolute number and the density are easily visible. Since my open-source GIS viewing systems (mostly QGIS and Mapserver) won't
Re: [postgis-users] Using grids transformation method with PostGis
I would also be interested in the answer to this question. Currently I export PostGIS data to shapefiles and use ogr2ogr to transform, if a grid shift is required. Perhaps the solution to this problem could be documented on the ST_Transform() page in the PostGIS manual? Cheers, Francis 2010/6/9 Luís de Sousa luis.a.de.so...@gmail.com Hello everyone, I'm trying to convert a series of spatial features from one SRS to another within PostGIS. Both are local systems for continental Portugal, for which a set of grid definition files have been made available by the University of Oporto. I downloaded these grid files and copied them into /usr/share/proj and /usr/local/share/proj. That was enough to get proj to start using them. The problem is, when invoking the same transformation from PostGis I'm getting errors in the range of tenths, sometimes hundreds, of metres. It seems like PostGis is failing to use the grid files. In attachment goes a log demonstrating the same transformation having different results with proj and PostGis. What am I missing? Is there any extra configuration needed for PostGis to use the grid files? Thank you, Luís ___ 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
[postgis-users] ArcGIS 9.3
Hi all, What is considered best practice for connecting to PostGIS 1.5 from ArcGIS 9.3? I am aware of the built-in Quick Import tool - are there any other tools that you use or would recommend? Cheers, Francis ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Check identical point
Try: ST_DWithin(point1, point2, 0.1) -F 2010/5/22 eehab hamzeh eeha...@hotmail.com Hello I want to check if two points are identical, the points has x,y,z coordinates and they are not exactly the same, i need to check the intersection between them with tolerance value. any direction of how to do that. Thank kind regards -- Hotmail: Powerful Free email with security by Microsoft. Get it now.https://signup.live.com/signup.aspx?id=60969 ___ 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
[postgis-users] line_interpolate_point does not return a point that intersects the line
Hi all, I think I've got a bit of a problem with snapping points to lines. I had thought that using line_interpolate_point(..., line_locate_point(...)) would do the trick, but this seems not to be the case: gis=# SELECT ST_AsText(v.the_geom), ST_SRID(v.the_geom), ST_AsText(r.the_geom), ST_SRID(r.the_geom) FROM road_segments AS r, venues AS v WHERE v.venue_id = 29 AND r.gid = 100982; st_astext | st_srid | st_astext| st_srid --+-+-+- POINT(415185.606066865 7827721.52951473) | 28353 | LINESTRING(415250.679102704 7827994.85292521,415218.694181662 7827642.44920486) | 28353 (1 row) gis=# SELECT ST_Intersects(ST_line_interpolate_point(r.the_geom, ST_line_locate_point(r.the_geom, v.the_geom)), r.the_geom) FROM road_segments AS r, venues AS v WHERE v.venue_id = 29 AND r.gid = 100982; st_intersects --- f (1 row) gis=# SELECT postgis_full_version(); postgis_full_version POSTGIS=1.4.1 GEOS=3.2.1-CAPI-1.6.1 PROJ=Rel. 4.7.1, 23 September 2009 USE_STATS (1 row) Any assistance would be much appreciated. Thanks, -Francis Markham ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Converting UTM to lat/lon using Postgis
Hi Mehmet, When you imported the shapefile, the SRID you are meant to specify is the SRID of the data, not the SRID you want. Since the data is in Dubai Local TM, the SRID is 3997. To get the transformation you want, try the following: select ST_AsText( ST_Transform(centroid(ST_SetSRID(the_geom, 3997)),4326)) from ult.test2 limit 5; Hope that helps, -Francis On 3 May 2010 17:03, Mehmet Erkek mer...@reidin.com wrote: Hi All, I havea a shape file which I imported it to a table called ‘test2’ (using SRID=4326). I want to get centroids of geometries in lat/lon. Here is what I run: select ST_AsText( ST_Transform(centroid(the_geom),4326)) from ult.test2 limit 5; and what I get: POINT(496597.996430787 2774798.21242881) POINT(496332.629887436 2793339.61534586) POINT(496302.627327975 2793317.82477318) POINT(496305.255261594 2793353.29646291) POINT(496241.538851096 2774826.65593589) So far all is fine except coordinates which are not latitude/longitude *My question is : How can I convert/get these coordinates in lat/lon?* This what I have in my prj file: PROJCS[Dubai Local Transverse Mercator (DLTM),GEOGCS[GCS_WGS_1984,DATUM[D_WGS_1984,SPHEROID[WGS_1984,6378137.0,298.257223563]],PRIMEM[Greenwich,0.0],UNIT[Degree,0.0174532925199433]],PROJECTION[Transverse_Mercator],PARAMETER[False_Easting,50.0],PARAMETER[False_Northing,0.0],PARAMETER[Central_Meridian,55.34],PARAMETER[Scale_Factor,1.0],PARAMETER[Latitude_Of_Origin,0.0],UNIT[Meter,1.0]] And this is my postgis version: POSTGIS=1.3.6 GEOS=2.2.3-CAPI-1.1.1 PROJ=Rel. 4.7.1, 23 September 2009 USE_STATS I appreciate any help. Thank you. --- *Mehmet ERKEK*** www.REIDIN.com http://www.reidin.com/ This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. Please Consider the Environment Before Printing This Email ___ 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] line_interpolate_point does not return a point that intersects the line
Mmm, so it is a floating point issue - the distance is 9.57711783686133e-12 meters. Is there anyway to specify a tolerance for ST_Intersects and similar predicates? -Francis On 3 May 2010 17:53, strk s...@keybit.net wrote: On Mon, May 03, 2010 at 04:41:09PM +1000, Francis Markham wrote: Hi all, I think I've got a bit of a problem with snapping points to lines. I had thought that using line_interpolate_point(..., line_locate_point(...)) would do the trick, but this seems not to be the case: Francis, thing is that the constructive functions have to deal with representability of numbers which means you won't always get the exact point of intersection but rather the best approximation of it. Instead the predicates (like ST_Intersects) give you the exact answer. You may try the distance functions to see how much the intersection point is off the line, should be a really low number. --strk; gis=# SELECT ST_AsText(v.the_geom), ST_SRID(v.the_geom), ST_AsText(r.the_geom), ST_SRID(r.the_geom) FROM road_segments AS r, venues AS v WHERE v.venue_id = 29 AND r.gid = 100982; st_astext | st_srid | st_astext| st_srid --+-+-+- POINT(415185.606066865 7827721.52951473) | 28353 | LINESTRING(415250.679102704 7827994.85292521,415218.694181662 7827642.44920486) | 28353 (1 row) gis=# SELECT ST_Intersects(ST_line_interpolate_point(r.the_geom, ST_line_locate_point(r.the_geom, v.the_geom)), r.the_geom) FROM road_segments AS r, venues AS v WHERE v.venue_id = 29 AND r.gid = 100982; st_intersects --- f (1 row) gis=# SELECT postgis_full_version(); postgis_full_version POSTGIS=1.4.1 GEOS=3.2.1-CAPI-1.6.1 PROJ=Rel. 4.7.1, 23 September 2009 USE_STATS (1 row) Any assistance would be much appreciated. Thanks, -Francis Markham ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ 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] line_interpolate_point does not return a point that intersects the line
D'oh. Totally forgot about DWithin! Problem, solved. Thanks very much! -Francis On 3 May 2010 18:24, strk s...@keybit.net wrote: On Mon, May 03, 2010 at 06:15:14PM +1000, Francis Markham wrote: Mmm, so it is a floating point issue - the distance is 9.57711783686133e-12 meters. Is there anyway to specify a tolerance for ST_Intersects and similar predicates? You may use DWithin() for a tolerance-based intersection approach. No built-in tolerance for others. --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ 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
