1

I am having Postgres database table with Lat Lon columns (degree, minutes, seconds) as shown below :

CREATE TABLE population (
  id double precision,
  idno text NOT NULL,
  place text,
  state text,
  district text,
  lat_deg double precision,
  lat_min double precision,
  lat_sec double precision,
  long_deg double precision,
  long_min double precision,
  long_sec double precision,
  dis_population double precision,
  date timestamp without time zone,
  "time" timestamp without time zone,
  CONSTRAINT pop_pk_idno PRIMARY KEY (idno) );
  • I already have PostGIS database. So,how would I migrate the above Postgres table with data into PostGIS database?

  • And after migration, is there any other modification required in migrated PostGIS table to expose it as Layer in Geoserver?

THanks all for your suggestion. I have executed below SQLs to spatially enable the table :

 1. SELECT AddGeometryColumn('population', 'latlongeo', 4326, 'POINT', 2);
 2. CREATE INDEX populationindex  ON population USING GIST ( latlongeo );

Now, how do i convert the latitude and longitude columns to a PostGIS POINT geometry as I have 3 columns for lat & long. (Deg, Min, Sec) and the below function allows only lat, long 2 columns: ST_GeomFromText(), ST_SetSRID().

Means how to convert the Lat&Lon Degree,Min,sec 6 columns to PostGIS POINT geometry ???

  • 2
    Creation of geometry tables from coordinates is well documented. If you have had trouble finding information or have been unsuccessful in applying what you have found, please include that in your question. – Scro Oct 12 '14 at 9:16
  • Sorry, but i dont have much knowledge in PostGIS and I need to do it ASAP. So, if any direct pointer/solution will be much helpful. – deval.techie Oct 12 '14 at 10:37
  • 2
    This is quite straight forward and well documented. You need to show that you have done some research and document that in your question with a specific problem, not just a broad question. – Mark Cupitt Oct 12 '14 at 11:14
  • 2
    What's your logic for having a floating-point ID column? Equivalence tests on real numbers are often flighty, so they're not often the best datatype choice for this purpose. You also seem to be wasting significant storage by using DOUBLE for degrees, minutes, and seconds... Even STRING would be more efficient. – Vince Oct 12 '14 at 16:18
  • @Vince. THanks for suggestion. However, these all client generated table and i cant ALTER these columns. I have edited my main post for the next question. – deval.techie Oct 13 '14 at 7:19
2

Let's assume that your 'deg' columns are +ve for North/East and -ve for South/West, and that all other metrics are +ve only, then making decimal degrees is straightforward.

SELECT AddGeometryColumn('population', 'latlongeo', 4326, 'POINT', 2);

UPDATE population SET
  latlongeo = ST_SetSRID(ST_MakePoint(
    sign(long_deg) * (abs(long_deg) + long_min/60 + long_sec/3600),
    sign(lat_deg) * (abs(lat_deg) + lat_min/60 + lat_sec/3600)
  ), 4326);

CREATE INDEX populationindex  ON population USING GIST ( latlongeo );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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