I am trying to setup a GIS system to use PostGIS and Geoserver. I have loaded all of the data into a table. The data that is received has long/lat coordinates in Degrees, Minutes, Seconds, Direction, each of which is a separate column. What I did is created a column (location) of type geography and I'm trying to give proper info for each record. The command that I am using is this (of course this is only in the Western Hemisphere):

UPDATE *tablename* SET location = ST_SetSRID(ST_MakePoint((lat_degrees+(lat_minutes+lat_seconds/60)/60), (-1*(long_degrees+(long_minutes+long_seconds/60)/60))), 4326);

My DMS -> DD conversions should be correct, and I've verified that by creating separate columns to test each individually. But the problem is, my geoserver doesn't detect the proper bounds. When I manually set the bounds (I set them to -180,-180,180,180) and check my data in Google Earth, it only zooms to 0,0 and I can find some horribly warped data, in Antarctica.

One thing to note... not all of my records include location data, could that be an issue? Or is ST_MakePoint simply not creating the right location for my data?

Thanks for the help!

up vote 2 down vote accepted

You've switched the order of your lat,lon. See the documentation for ST_MakePoint() and you'll see that it requires:

ST_SetSRID(ST_MakePoint(lon, lat),4326)

Then, after to make your changes, try verifying:

SELECT ST_AsText(location) FROM *tablename* LIMIT 10;
  • That's exactly it! I tested with the ST_AsText right after posting this and noticed I had switched them. They are showing now. Only remaining issue is that geoserver still can't figure out my bounds. Is that because of the empty values? – jpmaniac87 Jul 21 '14 at 16:06
  • Perhaps. I'd do a bit of logic before doing your UPDATE so that you're not trying to ST_MakePoint on null or empty values. Use an IF statement in your SQL, or COALESCE(lat_degrees, 0) will return the first non-null arguement... – mtn.biker Jul 21 '14 at 16:28
  • That is where I planned to go once everything is working, if nothing else for time. But, I even went back and made empty location values NULL and the issue is still there with Geoserver detecting bounds. Perhaps a Geoserver issue? – jpmaniac87 Jul 21 '14 at 18:21
  • I don't know the answer to that - I'd recommend writing a new question about Geoserver not calculating the extent of your table correctly due to some records containing null geometry. – mtn.biker Jul 21 '14 at 19:15

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.