[mapserver-users] csv join in mapscript/OGR

[Even Rouault]

Le lundi 01 juillet 2013 14:17:17, Minka Stoyanova a écrit :
> Hello,
> 
> I have a point shape layer and I would like to color each point according
> to a number in a  csv file= The csv file is:
> 
> id,color
> 1080,2
> 1090,4
> 11,2
> 12,5
> 
> 
> The ID column is the same as the NUMBER column in the shape file.
> 
> This is my layer:
> LAYER # Stations point layer 1 begins here
>     NAME         "meteo_oper"
>     CONNECTIONTYPE ogr
>     CONNECTION "../data/ESRI/meteo_oper.shp"
>     DATA "SELECT * FROM meteo_oper LEFT JOIN 'CSV:SPI201301.csv' ON
> meteo_oper.NUMBER=SPI201301.id"
>     STATUS ON
>     TYPE point
>        CLASS
>         SYMBOL 'circle'
>         SIZE 8
>         COLOR 0 255 0
>         OUTLINECOLOR 0 0 0
>       END
>   DEBUG ON
>   END # Stations point layer 1 ends here
> 
> And when I use OGR from command line to test my map-file:
> shp2img -m watersheds.map -o tr.png
> 
> I receive:
> msDrawMap(): Image handling error. Failed to draw layer named 'meteo_oper'.
> <br>
> msOGRFileOpen(): OGR error. ExecuteSQL(SELECT * FROM meteo_oper LEFT JOIN
> 'CSV:SPI201301.csv' ON meteo_oper.NUMBER=SPI201301.id) failed.
> SELECT from table CSV:SPI201301.csv failed, no such table/featureclass.
> <br> Segmentation fault

I don't reproduce the segmentation fault with mapserver latest dev version. I 
only get the error.

Anyway the solution is to fix your SQL request.

It should be "SELECT * FROM meteo_oper LEFT JOIN 'CSV:SPI201301.csv'.SPI201301 
ON meteo_oper.NUMBER=SPI201301.id"

Note the syntax for the table in the LEFT JOIN : 'datasource'.layer_name

More generally, I'd encourage you debugging your SQL requests with ogrinfo 
first.

e.g :

ogrinfo meteo_oper.shp -sql "SELECT * FROM meteo_oper LEFT JOIN 
'CSV:SPI201301.csv'.SPI201301 ON meteo_oper.NUMBER=SPI201301.id"

> 
> Any idea?
> Minka

-- 
Geospatial professional services
http://even.rouault.free.fr/services.html