markrodrigo/Llama-3.2-3B-Instruct-Spatial-SQL-1.0

1---2license: llama3.23base_model: meta-llama/Meta-Llama-3.2-3B4language:5- en6pipeline_tag: text-generation7tags:8- code9- spatial10- sql11- GIS12- PostGIS13---14 15**ENGLISH ONLY - Use 8b models for alternate languages.**16 17### Model Information18 19This model, Llama-3.2-3B-Instruct-Spatial-SQL-1.0, is an 3B, narrow use case, text to spatial SQL, lightly fine-tuned model. In general, its primary use case20is the Natural Language command adaptation of particular geographic spatial functions as normally defined in pure SQL. Data input should be a combination of an English prefix in the form of a question, and a coordinate prompt injection, likely from an active mapping system application coordinate list. Output is PostGIS spatial SQL.21 22There are four primary geographic functions released in version 1.0.23 24**Model developer**: Mark Rodrigo25 26**Github**: https://github.com/mprodrigo/spatialsql27 28**Model Architecture**: The model is a QLoRA / Supervised Fine Tuning (SFT)29 30### Model Input / Output Overview:31 32Input: Text plus coordinate prompt injection.33</br>34Output: **PostGIS spatial SQL**35</br>36NOTE: Inputs and outputs are in meters and or geographic decimal degrees WGS 84 coordinates.37 38| Function  | Question Input  | Geo Input | SQL Execution Output      |39|:---------:|:---------------:|:---------:|:-------------------------:|40| Area      | Area question   | Polygon   | Number - Area sq meters   |41| Centroid  | Center question | Polygon   | Point                     |42| Buffer    | Buffer distance | Point     | Polygon                   |43| Length    | Length question | Line      | Number - Length in meters |44 45### Example Prompt / Prompt File46 47<|begin_of_text|><|start_header_id|>system<|end_header_id|>48<p></p>49You are a helpful assistant. You are an expert at PostGIS and Postgresql and SQL and psql.50<p></p>51<|eot_id|><|start_header_id|>user<|end_header_id|>52 53\### Instruction: Write a PostGIS SQL statement for the following.54<p></p>55 56\### Input:57 58<p></p>59{input}60 61<p></p>62 63\### Response:64 65<|eot_id|><|start_header_id|>assistant<|end_header_id|>66 67 68### Examples69 70 71AREA72<p></p>73 74\### Input: What is the area for the polygon? : 'Polygon ((-3.7515154 40.3855551, -3.7514972 40.3856581, -3.7507005 40.3855767, -3.7507167 40.3854722, -3.7515154 40.3855551))'75<p></p>76 77\### Response: SELECT ST_Area(geog) As area FROM (select 'Polygon ((-3.7515154 40.3855551, -3.7514972 40.3856581, -3.7507005 40.3855767, -3.7507167 40.3854722, -3.7515154 40.3855551))' :: geography geog) subquery;78<p></p>79 80CENTROID81<p></p>82 83\### Input: What is the centroid for the polygon? : 'Polygon ((-3.6934636 40.4808785, -3.6933352 40.4811486, -3.6930125 40.4810598, -3.693141 40.4807897, -3.6934636 40.4808785))'84<p></p>85 86\### Response: SELECT ST_AsText(ST_Centroid(geog)) As centroid FROM (select 'Polygon ((-3.6934636 40.4808785, -3.6933352 40.4811486, -3.6930125 40.4810598, -3.693141 40.4807897, -3.6934636 40.4808785))' :: geography geog) subquery;87 88BUFFER89<p></p>90 91\### Input: What is the thousand meter buffer for the following point? : 'Point(-8.7522658 41.3862664)'92<p></p>93 94\### Response: SELECT ST_AsText(ST_Buffer(geog, 1000)) as buffer FROM (select 'Point(-8.7522658 41.3862664)' :: geography geog) subquery;95 96<p></p>97LENGTH98<p></p>99 100\### Input: How long is the line? : 'LINESTRING (-3.6976693 40.4263178, -3.6986082 40.4258729)'101<p></p>102 103\### Response: SELECT ST_Length(geog) As length FROM (select 'LINESTRING (-3.6976693 40.4263178, -3.6986082 40.4258729)' :: geography geog) subquery;104<p></p>105 106### A Few Known Question Variation Examples107 108<p></p>109AREA110<p></p>111What is the area for the geometry?112<p></p>113What is the area for this polygon?114<p></p>115CENTROID116<p></p>117What is the centroid for the geometry?118<p></p>119What is the center point of the polygon?120<p></p>121BUFFER122<p></p>123What is the 100 meter buffer for the following point?124<p></p>125Buffer the following point a thousand meters.126<p></p>127What is the 1000 meter buffer for the following point? 128<p></p>129LENGTH130<p></p>131What is the length of the line?132<p></p>133How long is this line?134 135 136### llama.cpp / Hyperparameter Recommendations For Inference137max context ~ 8,000 or lower138<p></p>139top k ~ 100140<p></p>141temp ~ .4-.5 or lower142 143### Agent Considerations144Agents are being considered as a separate project. Agents would mostly be related to pulling the coordinates from a mapping UI, and executing the SQL from responses against a PostGIS database.145 146### Further Reference - link this147https://postgis.net/docs/manual-3.3/PostGIS_Special_Functions_Index.html#PostGIS_GeographyFunctions148 149### Evaluation data150More information needed151 152### Training data153Custom synthetic154 155### Training hyperparameters156 157The following hyperparameters were used during training:158- learning_rate: 3e-05159- train_batch_size: 10160- eval_batch_size: 3161- distributed_type: multi-GPU162- num_devices: 2163- optimizer: Adam 8bit164- lr_scheduler_type: linear165- num_epochs: 5166 167### Training results168 169| Training Loss | Step | Validation Loss |170|:-------------:|:----:|:---------------:|171| 1.3788        |  10  |   1.3543        |172| 0.8504        |  20  |   0.8671        |173| 0.5990        |  30  |   0.6501        |174| 0.5836        |  40  |   0.5885        |175| 0.5713        |  50  |   0.5607        |176| 0.5481        |  60  |   0.5434        |177| 0.5221        |  70  |   0.5342        |178| 0.4983        |  80  |   0.5259        |179| 0.4914        |  90  |   0.5219        |180| 0.4754        |  100 |   0.5205        |181 182 183 184 185### Framework versions186 187- Transformers 4.45.1188- Pytorch 2.4.1189- peft 0.13.0190- Datasets 3.0.1191- Tokenizers 0.20.0