1

I have a table name erpAssets which has column name Region of type polygon. it contains entries of points also like (-79.4609576808001,43.9726680183837)).

I have written query which convert these points to geometry which is used by mapnick to generate tiles.

( SELECT replace(concat(concat('POINT',concat(trim(TRAILING ')'FROM concat('(',trim(LEADING '(' FROM region::TEXT)::TEXT) ),')')),''),',',' ') as data from "erpAssets" where parent=326815) as foo
) as points

My osm.xml file is as follows:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE Map [
<!ENTITY % entities SYSTEM "inc/entities.xml.inc">
%entities;
]>
<Map background-color="#bb0000" srs="&srs900913;" minimum-version="2.0.0">
  &fontset-settings;
 <Style name="theme_park">
     <Rule>
      <PointSymbolizer file="&symbols;/alpinehut.p.16.png" 

      />

    </Rule>

</Style>

&layer-shapefiles;
<Layer name="theme_park" status="on" srs="&osm2pgsql_projection;">
    <StyleName>theme_park</StyleName>
    <Datasource>
      <Parameter name="table">
        (SELECT ST_GeomFromText(data)
FROM
(
SELECT replace(concat(concat('POINT',concat(trim(TRAILING ')'FROM  concat('(',trim(LEADING '(' FROM region::TEXT)::TEXT) ),')')),''),',',' ') as data
 from "erpAssets" where parent=326815) as foo       
        ) as points

      </Parameter>
      &datasource-settings;
    </Datasource>
</Layer>
</Map>

Tiles are not generated and i am getting error on console as follows: Postgis Plugin: geometry name lookup failed for table '"erpassets"'. Please manually provide the 'geometry_field' parameter or add an entry in the geometry_columns for '"erpassets"'.

I have converted region column into geometry by aliasing then why i am not getting points on map?

  • A column of type polygon that contains points? I do not understand from what you are trying to build geometries. But to build a PostGIS geometry you will need to use a geometry constructor function like ST_Point(x, y). – Nicklas Avén May 15 '14 at 7:26
  • What's in &osm2pgsql_projection;? If 900013, that's an error: you need to specify the data is in WGS-84. – Ilja Zverev May 15 '14 at 7:34
  • @IljaZverev i am newbie to this can u tell me where to check this and change it to WGS-84? – user2174920 May 15 '14 at 7:42
  • @NicklasAvén check my select query which make required geomtery from regions of my points – user2174920 May 15 '14 at 7:43
  • @IljaZverev thanks i have changed that in my settings.xml file now tiles are generated but i cant see points of my image ? – user2174920 May 15 '14 at 7:54
1

How applications use PostGIS is they look up the metadata for geometry columns in the geometry_columns view (formerly a table). How you have set up the query is that each time regions, a text column, is translated and converted to a geometry column. However, since this result is part of a query, and not part of a table, it does not appear in geometry_columns, as Mapnik expects.

Add a geometry column and update your source table:

ALTER TABLE "erpAssets" ADD COLUMN geom(POINT,4326);

UPDATE "erpAssets" SET
  geom = ST_GeomFromText('SRID=4326;POINT' ||
              replace(substr(region, 2, length(region) - 2), ',', ' '));

Then update your Mapnik query to something simpler, like:

SELECT geom
FROM "erpAssets"
WHERE parent=326815;

(Note: use the SRID that applies to your question. I'm only guessing 4326)

  • thanks for replying me back. sir i have long,lat data but maonik is using srs900913 for projection. and it is only drawing one point. when i change projection to srs4326 then it is giving error – user2174920 May 16 '14 at 5:15

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.