1

I am stuck converting a geometry(MultiLineString) into coordinates. The following I did.

  1. I download the data from this Austin Contours data link

  2. I import the shape files using PGAdmin III and inserted around 894345 data.

  3. In last column I got the following geom MultiLineString.

    01050000000100000001020000002C00000000000080189B47419A9999D95F3C6341C2F528DC089B4741E17A14E65F3C6341A4703D2A079B47418FC2F5E85F3C634152B81EC5059B4741000000F05F3C634148E17AB4049B4741713D0AF75F3C634100000060039B474100000000603C63411E85EBB1019B47413E0AD723603C634133333313009B474152B81E3D603C6341E27A14EEFF9A4741E17A143E603C6341D7A3703DFE9A4741D7A37045603C63413E0AD703FC9A47419A999949603C63410AD7A3D0FA9A4741D7A37045603C63411E85EB31F89A47417B14AE2F603C6341F6285CCFF79A47410AD7A330603C634185EB5118F69A4741AE47E13A603C6341E27A140EF49A4741C2F5284C603C6341000000E0F39A4741E17A144E603C63419A9999F9F19A4741F6285C67603C6341703D0AF7F09A4741713D0A77603C634148E17AF4EF9A4741295C8F82603C6341B81E856BEF9A47415C8FC285603C63417B14AE67EE9A47417B14AE87603C634190C2F5A8EC9A4741EC51B88E603C63417B14AE27EB9A4741333333A3603C634190C2F528E99A4741E17A14C6603C63413E0AD723E19A4741A4703D82613C63410AD7A350DF9A474152B81EB5613C634166666666DE9A474148E17AD4613C634166666626DD9A47418FC2F500623C634152B81E85DB9A4741E17A1436623C63413E0AD723DA9A474100000060623C634100000020D99A47415C8FC27D623C634114AE4781D79A4741666666A6623C634100000040D69A4741AE47E1C2623C6341703D0AD7D49A4741EC51B8DE623C6341C2F5283CD09A474133333333633C6341EC51B8FECE9A4741CDCCCC4C633C6341333333B3CD9A47416666666E633C6341D7A3705DCC9A4741B81E8593633C63410AD7A3B0C99A4741EC51B8DE633C6341C2F5289CC89A47417B14AEFF633C63415C8FC2B5C69A4741B81E854B643C6341E27A14AEC59A4741EC51B876643C634185EB5118C59A47419A999991643C6341

When I converted this into coordinates from online link it gives the wrong coordinated data as given below

{"type":"MultiLineString","coordinates":[[[3094065,10085118.8],[3094033.7199999997,10085119.19],[3094030.33,10085119.28],[3094027.54,10085119.5],[3094025.41,10085119.72],[3094022.75,10085120],[3094019.3899999997,10085121.120000001],[3094016.15,10085121.91],[3094015.8600000003,10085121.94],[3094012.48,10085122.17],[3094008.0300000003,10085122.3],[3094005.63,10085122.17],[3094000.3899999997,10085121.49],[3093999.62,10085121.52],[3093996.19,10085121.84],[3093992.1100000003,10085122.379999999],[3093991.75,10085122.44],[3093987.95,10085123.23],[3093985.9299999997,10085123.72],[3093983.91,10085124.08],[3093982.84,10085124.18],[3093980.81,10085124.24],[3093977.3200000003,10085124.46],[3093974.31,10085125.1],[3093970.3200000003,10085126.19],[3093954.2800000003,10085132.07],[3093950.63,10085133.66],[3093948.8,10085134.64],[3093946.3,10085136.03],[3093943.04,10085137.69],[3093940.2800000003,10085139],[3093938.25,10085139.93],[3093935.01,10085141.2],[3093932.5,10085142.09],[3093929.6799999997,10085142.96],[3093920.4699999997,10085145.6],[3093917.99,10085146.4],[3093915.4,10085147.45],[3093912.73,10085148.61],[3093907.38,10085150.96],[3093905.2199999997,10085151.99],[3093901.42,10085154.36],[3093899.3600000003,10085155.71],[3093898.19,10085156.55]]]}

Which is not the lat long. I didn't understand why I getting this I am using the following query to get the data from the PostgreSQL database tables.

BEGIN 
SELECT row_to_json(fc.*) as dparcel into ac
FROM 
(
  SELECT  'FeatureCollection' AS type, 
    array_to_json(array_agg(f.*)) AS features 
  FROM 
  ( 
    SELECT  'Feature' AS type,  
      ( 
        SELECT  row_to_json(t) as properties  
        FROM 
        ( 
          select * from austin.austin_contours WHERE gid = adp.gid
        ) t 
      ),
    ST_AsGeoJSON(geom)::json  as geometry
    FROM austin.austin_contours  adp
    LIMIT 20
  ) f 
) fc; 
return;
END;

Here geom is the column name of austin.austin_contours table.

Improve this question
7
  • 2
    the data is in a projected CRS, the coordinates meter (or possibly even feet) in relation to the reference axes; do you know the CRS SRID of that data? – geozelot Jun 28 '18 at 11:40
  • No, I am sorry... I don't know the CRS SRID, because I import the data directly from shapefile via PostGIS Shapefile and DBF Loader. But yes I import the shape file the import/export manager window default was 0 I just saw that. I was also wondering the the string is right or not ? – Ganesh Gothi Jun 28 '18 at 11:52
  • the string is fine, it just need a SRID, at least to be transformed to the geographic coordinates you seem to want. what tools do you have available? QGIS? or are you familiar with the OGR command line tools (i.e. ogrinfo)? the shapefiles should have the CRS info in it´s metadata or go check the providers homepage for info on it. – geozelot Jun 28 '18 at 11:59
  • I didn't work on QGIS recently, so not so much aware on this and don't have software available with me and not worked on OGR command line tools yet. I just try to show the shape files on mapshaper.org and get the other format data as I need. – Ganesh Gothi Jun 28 '18 at 12:06
  • I got the CRS from QGIS. CRS is "[+proj=lcc +lat_1=30.11666666666667 +lat_2=31.88333333333333 +lat_0=29.66666666666667 +lon_0=-100.3333333333333 +x_0=699999.9999999999 +y_0=3000000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=us-ft +no_defs]". Now what need to do, please help me – Ganesh Gothi Jul 2 '18 at 10:19
0

According to the .prj file included it is stored in NAD_1983_StatePlane_Texas_Central_FIPS_4203_Feet which according to epgs.io is ESRI:102739 and probably EPSG:42307.

Improve this answer
5
  • ok, but we need CRS SRID of the data I imported which I am not able to get it, I thing we got this we can do something for string and get the right coordinate multiline string data, is that make sense ? – Ganesh Gothi Jun 28 '18 at 14:18
  • that is the SRID of your data – Ian Turton♦ Jun 28 '18 at 14:19
  • Ok...So EPSG:42307 is the SRID. What I need to do in that to get the right coordinates of the multilinestring data given above. – Ganesh Gothi Jun 29 '18 at 5:31
  • I set the SRID to 4326 now from the following query. ALTER TABLE austin.austin_contours ALTER COLUMN geom SET DATA TYPE geometry(MULTILINESTRING, 4326) USING ST_SetSRID(geom,4326); Again when I select the data the out of coordinate is same, what to do to get the right data now. – Ganesh Gothi Jun 29 '18 at 6:42
  • see gis.stackexchange.com/questions/209099/postgis-reproject-data – Ian Turton♦ Jun 29 '18 at 7:29

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.