Llama-3.2-3B-Instruct-Spatial-SQL-1.1-Docs

ENGLISH ONLY - Use 8b models for alternate languages.

Model Information

This model, Llama-3.2-3B-Instruct-Spatial-SQL-1.1, is an 3B, narrow use case, text to spatial SQL, lightly fine-tuned model. In general, its primary use case is 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.

There are five primary geographic functions released in version 1.1.

Model developer: Mark Rodrigo

Github: https://github.com/mprodrigo/spatialsql

Model Architecture: The model is a QLoRA / Supervised Fine Tuning (SFT)

Model Input / Output Overview:

Input: Text plus coordinate prompt injection. </br> Output: PostGIS spatial SQL </br> NOTE: Inputs and outputs are in meters and or geographic decimal degrees WGS 84 coordinates.

</br>

Example Prompt / Prompt File

<|begin_of_text|><|start_header_id|>system<|end_header_id|>

<p></p> You are a helpful assistant. You are an expert at PostGIS and Postgresql and SQL and psql. <p></p> <|eot_id|><|start_header_id|>user<|end_header_id|>

### Instruction: Write a PostGIS SQL statement for the following.

<p></p>

### Input:

<p></p> {input} <p></p>

### Response:

<|eot_id|><|start_header_id|>assistant<|end_header_id|>

Examples

AREA

<p></p>

### 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))'

<p></p>

### 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;

<p></p>

CENTROID

<p></p>

### 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))'

<p></p>

### 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;

BUFFER

<p></p>

### Input: What is the thousand meter buffer for the following point? : 'Point(-8.7522658 41.3862664)'

<p></p>

### Response: SELECT ST_AsText(ST_Buffer(geog, 1000)) as buffer FROM (select 'Point(-8.7522658 41.3862664)' :: geography geog) subquery;

<p></p> LENGTH <p></p>

### Input: How long is the line? : 'LINESTRING (-3.6976693 40.4263178, -3.6986082 40.4258729)'

<p></p>

### Response: SELECT ST_Length(geog) As length FROM (select 'LINESTRING (-3.6976693 40.4263178, -3.6986082 40.4258729)' :: geography geog) subquery;

<p></p> DISTANCE <p></p>

### INPUT: How far apart is the point and line? : 'Point(-109.87549823 38.60574249)' 'LineString(-109.24324628 38.76349931, -109.4821773 38.6875815)'

<p></p>

### Response: SELECT ST_Distance(gg1, gg2) As distance FROM (select 'Point(-109.87549823 38.60574249)' :: geography gg1, 'LineString(-109.24324628 38.76349931, -109.4821773 38.6875815)' :: geography gg2) subquery;

<p></p>

A Few Known Question Variation Examples

<p></p> AREA <p></p> What is the area for the geometry? <p></p> What is the area for this polygon? <p></p> CENTROID <p></p> What is the centroid for the geometry? <p></p> What is the center point of the polygon? <p></p> BUFFER <p></p> What is the 100 meter buffer for the following point? <p></p> Buffer the following point a thousand meters. <p></p> What is the 1000 meter buffer for the following point? <p></p> LENGTH <p></p> What is the length of the line? <p></p> How long is this line? <p></p> DISTANCE <p></p> The distance between the polygon and the line is? <p></p> What is the distance between the points? <p></p> How far apart are the two lines?

llama.cpp / Hyperparameter Recommendations For Inference

max context ~ 8,000 or lower

<p></p> top k ~ 100 <p></p> temp ~ .4-.5 or lower

Agent Considerations

Agents 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.

Further Reference - link this

https://postgis.net/docs/manual-3.3/PostGIS_Special_Functions_Index.html#PostGIS_GeographyFunctions

Evaluation data

More information needed

Training data

Custom synthetic

Training hyperparameters

The following hyperparameters were used during training:

• learning_rate: 2e-4
• train_batch_size: 10
• eval_batch_size: 3
• distributed_type: multi-GPU
• num_devices: 2
• optimizer: Adam 8bit
• lr_scheduler_type: linear
• num_epochs: 5

Training results

Framework versions

• Transformers 4.46.1
• Pytorch 2.5.1
• peft 0.13.2
• Datasets 3.1.0
• Tokenizers 0.21.0